Force a 0 in Month Function (1 Viewer)

[mstorer]

I am working on a report that excludes all information prior to the current month. I have a date field in short date format and it would be easy enough to simply put in a new date constraint each month. I would like automate this and I am going down the path of pulling out the year and month using the following expression in my query:
MonthAndYear: Year([Accounting Date]) & Month([Accounting Date])
I placed the following contraint to eliminate this month:
<Year(Now()) & Month(Now())

This would work exept that the month in the above expressions is not always two characters (i.e. 01, 09, 12). Is there a way to force a 0 in the above expression for January through September? Or, please let me know if there is a better approach. Thanks for any insight!

 

[The_Doc_Man]

Look at the DateDiff function using units of Days. If the difference between the beginning of the month and the date in question is negative, it is from a prior time.

Now, to get the date of the first of the month, that is a DateAdd function and a DatePart function.

You want to take the DatePart function to get the day of the month as a number from 1-31. Then subtract that number from 1. Then do DateAdd on today and that negative or zero difference. The resulting date is the day of the first of the month.

 

[Pat Hartman]

Use the Format() function to extract the year and month.

MonthAndYear: Format([Accounting Date],"yyyymm")
and in criteria

< Format(Date(),"yyyymm")

BTW - Only use the Now() function when you need the time component of a date.

 

[mstorer]

Brilliant! I tried both methods and they worked fine. I have used Access for several years now and am embarrased to say I had never used either the DatePart or the Format Functions. I am positive they will be a real benefit in the future. Thanks again.