Quarters of Current or Previous Year

[Guest]

I have the following query:

SELECT loanActivities.officerNumber, Sum(loanActivities.Service_Charge)
FROM loanActivities
WHERE (((loanActivities.Date_of_Loan) Between #1/1/2006# And #3/31/2006#))
GROUP BY loanActivities.officerNumber;

How do I change the WHERE clause so that it always totals the records in the
first quarter of the current year? I don't want to have to change the dates
in the WHERE clause everytime the year changes. Also, here's another twist:
In January of each year, I need it to total the records for the first quarter
of the previous year. (Only in January!)

Many thanks!

 

[Van T. Dinh]

.... BETWEEN IIf( Month(Date()) > 1,
DateSerial(Year(Date()), 1, 1),
DateSerial(Year(Date()) - 1, 1, 1) )
AND IIf( Month(Date()) > 1,
DateSerial(Year(Date()), 3, 31),
DateSerial(Year(Date()) - 1, 3, 31) )

 

[John Spencer]

Use Date Serial function to build the date range.

SELECT loanActivities.officerNumber, Sum(loanActivities.Service_Charge)
FROM loanActivities
WHERE loanActivities.Date_of_Loan
Between DateSerial(Year(Date()) - IIF(Month(Date())=1,1,0),1,1) and
DateSerial(Year(Date())-IIF(Month(Date())=1,1.0),3,31)
GROUP BY loanActivities.officerNumber;

 

[Guest]

Thank you for the quick reply! I haven't tried it yet, but looks like to me
it will work. I'll post another query if it doesn't.

 

[Van T. Dinh]

I can't put a reason on it but I like John Spencer's suggestion better than
mine.

However, if you want to use John's expression, make sure you change the
period (.) in the second DateSerial to comma (,).

Thanks for jumping in, John.