Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #9092

Re: Matching dates

Show all headers | View raw

On 01/03/2014 11:55:23, Ulrich Möller wrote:
> Am 01.03.2014 10:18, schrieb Phil:
>> On 28/02/2014 23:39:15, "David Hare-Scott" wrote:
>>> Phil wrote:
>>>> I am trying to match up dates that accounts are due to be received,
>>>> with the
>>>> dates that they actually are received. Most of the money expected is
>>>> on a
>>>> recurring monthly basis and normally the receipt comes in within
>>>> about 10
>>>> days of the expected date.
>>>>
>>>> So there is a TblAccounts
>>>>    AccountID as the primary key
>>>>
>>>> TblDueDates
>>>> AccountID FK
>>>> DueDate
>>>> TblTransactions
>>>> AccountID FK
>>>> TransDate
>>>>
>>>> I have a form with FromMonth & ToMonth, so I want to match up the paid
>>>> transactions within those months . By comparing the Month(DueDate)
>>>> with
>>>> Month(TransDate) and Year(DueDate) with Year(TransDate), I get a
>>>> reasonable
>>>> result, but it goes wrong when say the DueDate  1st March and the
>>>> TransDate
>>>> is 28th February. DateDiff doesn’t work either as there are numerous
>>>> nearly
>>>> matching DueDates and TransDates for each account.
>>>>
>>>> Any ideas how to solve this “fuzzy” logic problem please
>>>>
>>>> Phil
>>>>
>>> I don't know what you are trying to do. What is the criteria for selecting
>>> these records? When you say it "goes wrong" what is your SQL, what do you
>>> want to happen and what does happen?
>>>
>>>
>>> David
>>>
>>>
>> Thanks David, I was trying to avoid posting the SQL as it's a bit of a
>> mouthful  .. but here it is
>>
>> SELECT DueDate.DueDate, Investments.SecurityName, DueDate.Amount,
>> Format([DueDate],'dd mmm') AS DueDt,
>> IIf(Min(Abs(DateDiff('d',[DueDate],[TransDate]))<10),'OK','No') AS Paid,
>> DLookup('Credits','Transactions','InvestmentID  ' &
>> [Investments].[InvestmentID] & ' And Cint(Month(TransDate))  ' &
>> CInt(Month([DueDate].[DueDate])) & ' And Cint(Year(TransDate))  ' &
>> CInt(Year([DueDate].[DueDate]))) AS CreditFound FROM (SecurityType INNER JOIN
>> (Investments INNER JOIN DueDate ON Investments.InvestmentID
>> DueDate.InvestmentID) ON SecurityType.SecurityTypeID
>> Investments.SecurityTypeID) LEFT JOIN Transactions ON
>> Investments.InvestmentID  Transactions.InvestmentID WHERE
>> (((CInt(Month([DueDate])))>Int(2) And (CInt(Month([DueDate])))<Int(3))
>> AND ((IIf(Not IsNull([TransDate]),CInt(Month([TransDate])),0))>Int(2) And
>> (IIf(Not IsNull([TransDate]),CInt(Month([TransDate])),0))<Int(3))) OR
>> (((CInt(Month([DueDate])))>Int(2) And (CInt(Month([DueDate]))) <Int(3))
>> AND ((Transactions.TransDate) Is Null)) GROUP BY DueDate.DueDate,
>> Investments.SecurityName, DueDate.Amount, Format([DueDate],'dd mmm'),
>> ELookup('Credits','Transactions','InvestmentID  ' &
>> [Investments].[InvestmentID] & ' And Cint(Month(TransDate))  ' &
>> CInt(Month([DueDate].[DueDate])) & ' And Cint(Year(TransDate))  ' & CInt
>> (Year([DueDate].[DueDate]))), SecurityType.SecurityType,
>> SecurityType.AssetType, Investments.InvestmentClosed, Month([DueDate]) HAVING
>> (((SecurityType.AssetType)<>3) AND ((Investments.InvestmentClosed)úlse))
>> ORDER BY DueDate.DueDate;
>>
>> The Investments table is the Accounts table that I mentioned before.
>>
>> The CInt(2) and CInt(3) refer to asking for results from February to March
>>
>> I am trying to find CreditFound where the date (TransDate) matches as closely
>> as possible to the expected Date (DueDate). It works fine except in the case
>> of the DueDate being March 1st and the TransDate being February 28th. In this
>> case the CreditFound is Null (because the months are different), but the Paid
>> comes out correctly as OK. The SecurityType Table is basically whether the
>> investment is a credit or debit item and is just used as a filter to select
>> credits (SecurityType.AssetType <>3). The Paid is calculated arbitrarily as
>> "OK" if the 2 dates are within 10 days of each other.
>>
>> I suspect there will be a similar problem with the years being different with
>> a DueDate in January and a TransDate in the oreceding December. I feel I
>> should be using Abs(DateDiff("d",DueDate, TransDate)) but for any investment
>> this will occur on a monthly basis.
>>
>> Thanks
>>
>> Phil
>>
>> ---
>> This email is free from viruses and malware because avast! Antivirus protection is active.
>> http://www.avast.com>
> Hi Phil,
> 
> simple select a constant base date for example CDate(-1) and then
> convert each date you want to compare in a number of days since then.
> ex. for a duration check you can create a lowDate (CountOfDays
> -duration/2) and a highDate(CountOfDays + duration/2).
> CountOfDays is the difference in days between the test day and the base
> date.
> 
> Ulrich
> 
> 

Don't see how that would work.
Suppose I have a pension of Ł100 due on the 1st of each month and the
payments received in the second 2 columns

So
DUEDATE AMOUNT EXPECTED  TRANSDATE  AMOUNT RCD.

Jan 1 Ł100 Jan 3 Ł110 Feb 1 Ł100 Jan 29 Ł90 Mar 1 Ł100 Mar 1 Ł100
Apr 1                      Ł100 
May 1                     Ł100                       Apr 28             Ł100

So basically I am saying that if the date the money is received (TransDate)
is within 10 days of the date that it is due, then show both amounts on the
same line on a contiinuous form.

Phil

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Matching dates "Phil" <phil@stantonfamily.co.uk> - 2014-02-28 23:20 +0000
  Re: Matching dates "David Hare-Scott" <secret@nospam.com> - 2014-03-01 10:39 +1100
    Re: Matching dates "Phil" <phil@stantonfamily.co.uk> - 2014-03-01 09:18 +0000
      Re: Matching dates Ulrich Möller <knobbi38@usenet.arcornews.de> - 2014-03-01 12:55 +0100
        Re: Matching dates "Phil" <phil@stantonfamily.co.uk> - 2014-03-01 17:57 +0000
          Re: Matching dates "Phil" <phil@stantonfamily.co.uk> - 2014-03-01 18:00 +0000
            Re: Matching dates Patrick Finucane <patrickfinucanetx@gmail.com> - 2014-03-01 15:23 -0800
              Re: Matching dates "Phil" <phil@stantonfamily.co.uk> - 2014-03-02 09:32 +0000

csiph-web