Help needed with Month(Now())-1 criteria.

[Jman883]

I am building a query the display records for the past month. It is my under standing that Month(Now())-1 would accomplish this.

My problem is if I remove Month(Now())-1 the query will show all records as it should, but when Month(Now())-1 is added or Month(Now())-2 or Month(Now())-3 no data is displayed.

Any Idea?

Thank you for your help,

 

[ChrisO]

Try placing the cursor on the word Month and hit the F1 key.

The Month() function returns an integer from 1 to 12.

Subtracting 1 returns 2 when your computers clock is set to March.

2 may not be what your query expects.

Therefore, post the query.

Regards,
Chris.

 

[ajetrumpet]

what is the field that you are using the MONTH() function on? is it a date/time datatype?

this might help too:

http://www.access-programmers.co.uk/forums/showthread.php?t=157264

 

[Mike375]

To do that you first create a field based on your date field as in

Month([YourDateField]) which will give results from 1 to 12 and then apply your your Month(Date())-1 to the criteria of that field.

 

[HiTechCoach]

Try:

Between DateSerial(Year(Date()), Month(Date())-1,1) and DateSerial(Year(Date()), Month(Date()),0)

 

[ajetrumpet]

I think he's got enough help now! LOL

 

[HiTechCoach]

Can you have to many options?

 

[ChrisO]

Sorry if I sounded a little abrupt before but my blood pressure was dropping and I really didn’t have time to be nice… so just get the job done.
I hit 70/50 which is not my record of 53/40 (still conscious enough to record the BP in a database though )

Databases will be the death of me yet.

 

[Jman883]

WOW, I just got back from lunch, time to get this thing sorted out.

I will post back with the solution.

Thanks

 

[Mike375]

Sorry if I sounded a little abrupt before but my blood pressure was dropping and I really didn’t have time to be nice… so just get the job done.
I hit 70/50 which is not my record of 53/40 (still conscious enough to record the BP in a database though )

Databases will be the death of me yet.

I find low blood pressure and Access to be mutually exclusive

 

[ChrisO]

Back up to 110/70 which is about normal for me.

 

[ajetrumpet]

I find low blood pressure and Access to be mutually exclusive

I find either OR to be the devil speaking to me on my shoulder.

 

[ChrisO]

Two weeks ago I left the pub in an ambulance, same thing but not a pretty sight.

 

[Jman883]

Thanks HiTechCoach, you have help me again.
After c/p your code in my query it pulled the records for February

Could you please explain the logic in this code?

Between DateSerial(Year(Date()), Month(Date())-1,1) and DateSerial(Year(Date()), Month(Date()),0)

 

[Jman883]

ChrisO,
Thanks for the F1 tip very helpful.

 

[Mike375]

Thanks HiTechCoach, you have help me again.
After c/p your code in my query it pulled the records for February

Could you please explain the logic in this code?

Between DateSerial(Year(Date()), Month(Date())-1,1) and DateSerial(Year(Date()), Month(Date()),0)

It is giving the dates from 1stFeb to 28thFeb but it would not work if the year was less than 2009.

 

[Jman883]

Will I need to change anything when we reach 2010, or 2011?

 

[Mike375]

Will I need to change anything when we reach 2010, or 2011?

No, because it is based on Date()

Try this experiment. If you look at HighTechs code you will see there are two parts. Create two new fields in a query (or unbound textboxes) and put each section in one and you will see it is displaying dates.

The soution I gave you creates another field based on your date field and every record will be either 1, 2, 3 through to 12 and is independent of the year. Criteria such as Month(Date())-1 or whichever finds it Ok because the Month(Date())-1 is giving 2 as an answer and thus finds all the records in the created field with a 2.

The advantage of highTechs is you don't create another field the advantage in mine is it is independent of year and so suits DateofBirth fields etc for mail merge and whatever.

 

[HiTechCoach]

It is giving the dates from 1stFeb to 28thFeb but it would not work if the year was less than 2009.

Mike375,

I don't understand why it will not always work. My recommendation will work doer any uear. It will handle leap years, etc.

The OP said in the original post:

I am building a query the display records for the past month

My example will always return the date range.


I do not understand why it will not work for if the year was less than 2009. Would you please explain why so that I can learn what I missed?

 

[Mike375]

Mike375,

I don't understand why it will not always work. My recommendation will work doer any uear. It will handle leap years, etc.

It would not find dates where the year was less than the current year such as people born in February etc.