12 month trend

[Jamet1234]

I have made a query that I want to show all records with the the current month and all 11 months earlyer. This is the Criteria that I used. Is my syntax correct?

Between Month(Date()) And Month(Date()-11)

I put it in a field called Month([Date])

Thanks

Anthony

 

[John_W]

Not quite. Month(date()) will currently return 10. If you subtract 11 from that you're not really where you want to be.

You need a method which takes account of the year also. I'd suggest using the DateAdd function to subtract 11 months from today's date.

 

[Jamet1234]

Can you show me an example please?

Thanks
Anthony

 

[raskew]

Hi -

Try this in the criteria cell of your date field.

 between dateserial(year(date())-1, month(date())+1,1) and dateserial(year(date()), month(date())+1,0)

It should return records between 1-Dec-2006 and 30-Nov-2007.

Be advised that Month() is an Access function (reserved word) and shouldn't be used as a field name.

HTH - Bob

 

[Jamet1234]

Thanks reskew for the code.
I was not using Month() as a field name in a table, just in the query column with the criteria.
I my trying to learn this as I go can you please define the syntax of the code so that I can better understand what is going on.


Thanks
Anthony

 

[raskew]

Hi Anthony:

Get rid of

 I put it in a field called Month([Date])

Go back up to my first post and insert the posted code into the criteria cell of your date field. Should work.

Bob

 

[Jamet1234]

I did it works great, just wanted some sytax definition for next time. so that I can understad what each part of the code you gave me does. It works great. Sorry if my last post was hard to understand.

Thanks
Anthony