Date Criteria - Last Month same day

[Guest]

I'm trying to run a query from the Sales Table that will give me a snapshot
of today's sales. I want to compare today's sales with sales from one month
ago, two months ago, three months ago etc. in the corresponding fields in the
same query.

How can I do this in Access query?

Please help.

 

[Guest]

Use calculated fields for each month you want to include:

ThisMo: IIf([SalesDate] = Date(),[SalesAmt],0)
LastMo: IIf([SalesDate] = DatAdd("m", -1, Date()), [SalesAmt],0)
2Mo: IIf([SalesDate] = DatAdd("m", -2, Date()), [SalesAmt],0)

And so on.

 

[Van T. Dinh]

.... but beware that this comparison is very rubbery ...

What if today's date were 31/03/2006?