Need June 1st date for each year

[Gina Maylone]

Hello everyone! Happy NEW YEAR!



I have a query that I'm trying to find out the age of an invoice. The invoice is sent once a year. Basically I need this (6/1 stays the same, but I need to change it for the current year.

Age: DateDiff("d",#6/1/2018#,Date())

Another thing I tried

Age2: GetElapsedDays(#6/1/2018#-Date())

and then for a third component I have

CurYear: DatePart("yy",Date())

and I know that it doesn't have to be this difficult! Can anyone point me in the easier direction? I thank you in advance.

[kd2017]

I don't understand the need for the manual date entry 6/1/yyyy? Do the invoices not have a date field? age would then simply be Age: Date() - InvoiceDateFieldName

That said I like to use DateSerial() when I'm building a date. DateSerial( year, month, day ) => DateSerial( Year(Date()), 6, 1) would return June 1st of the current year.

[Gina Maylone]

I got it! And yes kd2017 there is a date field,

SELECT DISTINCTROW Members.MemberID_PK, [dbamount]-[cramount] AS BAL, IIf([SpouseLastName] Is Null And Not ([Spouse] Is Null),[FirstName] & " and " & [Spouse] & " " & [LastName],[FirstName] & " " & [LastName]) AS ComboName, ACTIVEROADSACCOUNTS.DBAmount, ACTIVEROADSACCOUNTS.DBDate, ACTIVEROADSACCOUNTS.Details, ACTIVEROADSACCOUNTS.CRAmount, ACTIVEROADSACCOUNTS.CRDATE, ACTIVEROADSACCOUNTS.COMMENTS, AccountStatus.Status, GetElapsedDays([duedate]-Date()) AS Age2, DateSerial(Year(Date()),6,1) AS Duedate
FROM (Members INNER JOIN AccountStatus ON Members.MemberID_PK = AccountStatus.MemberID) INNER JOIN ACTIVEROADSACCOUNTS ON Members.MemberID_PK = ACTIVEROADSACCOUNTS.MemberID_PK;