Access Query Last Year Month Total 1

[dgp2]

Could someone please help. I am trying to write a query that will pull all the orders for the current month LAST YEAR. For instance since this is February 2003 I would want all the orders for February of 2002. I would like this query to be automated so I don't have to put in the new parameters every month. The field name in my table is named DateEntered and its data type is a date field. Any help would be appreciated.

 

[Nikita6003]

Hiya

Try:

>=DateSerial(Year(Now())-1,Month(Now()),1) And 
<=DateSerial(Year(Now())-1,Month(Now()),IIf(Month(Now())=1 Or Month(Now())=3 Or Month(Now())=5 Or Month(Now())=7 Or Month(Now())=8 Or Month(Now())=10 Or Month(Now())=12,31,IIf(Month(Now())=2,28,30)))

This uses Access SQL functions & was created in the Design window in Access 2000

If you want it to take leap years into account, change the 28 in the last part of the function to another IIF to test if Year(Now()) is divisible by 4

HTH!

Cheers
Nikki

 

[dgp2]

Thank you very, very much. I can't tell you how much time I've spent on this.

I don't understand how to check for the leap year, can you explain this?

Thanks again.

 

[Nikita6003]

Yup. You need to test whether the year's divisible by 4 - now I don't think Access has the MOD function like Excel (this returns the rest left by a division, rather than the result of the division), but you could simulate that with this:

IIF( (Year(Now()-1) / 4 - INT((Year(Now()-1) / 4) = 0 , 29, 28)

You're testing whether dividing by 4 leaves no rest. If it doesn't you have a leap year and therefore the month of feb has 29 days.

HTH!

Cheers
Nikki

 

[dgp2]

Thanks for explaining. I really appreciate all your help.