DateAdd and DatePart

K

Kate

I have set up a query which gives me month end summaries of data. I
want the date to update automatically, such that if it is september I
want the query to show me everything in which a particular field has
august. I have set the following criteria:

DatePart("m",DateAdd("m",-1,Date()))

That works fine at the moment. ie it is currently september, and it
shows me all the august values. Will this formular still work when it
is january and the criteria should be december?
 
K

KARL DEWEY

Insert a field like this in your query grid -
Year: DatePart("yyyy",[YourTable].[YourField]) & Right("0" &
DatePart("m",[YourTable].[YourField]),2)

Then use this as criteria on that field for last month data --
DatePart("yyyy",Date()-Day(Date())) & Right("0" &
DatePart("m",Date()-Day(Date())),2)
 
K

Kate

So you're saying it won't work the way I've got it, or just that its
not the best way of doing it?
 
K

Kate

Ok I see what you mean now - I have to specify the year. Where you say
"[YourTable]" above, you mean the table that the query runs off?
 
J

John Vinson

I have set up a query which gives me month end summaries of data. I
want the date to update automatically, such that if it is september I
want the query to show me everything in which a particular field has
august. I have set the following criteria:

DatePart("m",DateAdd("m",-1,Date()))

That works fine at the moment. ie it is currently september, and it
shows me all the august values. Will this formular still work when it
is january and the criteria should be december?
Click to expand...

The simplest criterion, which will work fine across years, is to use
= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)
Click to expand...

The DateSerial function takes a year, month and day number and returns
a date/time value; it's smart enough to treat the 0th month of 2006 as
December, 2005.

John W. Vinson[MVP]
 
K

KARL DEWEY

Correct. It takes your table and extracts the year of your date field and
concatenates a two digit month of your date field.

Then it takes the numerical date of today ( Date() ) and subtracts the
day of the month. Today is the 15th of the month so today minus 15 days will
be the last day of last month. It extracts the year and concatenates a two
digit month to compare with your date field.
 
Top