query not showing all data for last month

  • Thread starter Thread starter shane
  • Start date Start date
S

shane

I have written a query with the intent to select data from a table for the
last 12 months only and grouped by individual months.

My group by expression is:

(Year([customerdate])*12+Month([customerdate])-1)

My where expression is:

DateDiff("yyyy",[customerdate],Date())

Criterial is <=1.

Until the new year, these expressions worked together fine. Now it will
only pull data from the beginning of the most recent month. February will
all show up in April for example, but not all of March will.

I'm not sure what the fix is. Any help is appreciated.
 
If you want all data between the current date and one year ago then the
criteria would be applied to CustomerDate as follows:

BETWEEN DateAdd("yyyy",-1,Date()) AND Date()

If you want all dated for the prior 12 months. That is it's March 1 2010 and
you want all the data from Feb 1, 2009 to Feb 28, 2010.

BETWEEN DateSerial(Year(Date())-1,Month(Date())-1,1) AND
And DateSerial(Year(Date()),Month(Date()),0)

For March 1, 2009 to March 31, 2010

BETWEEN DateSerial(Year(Date())-1,Month(Date()),1) AND
And DateSerial(Year(Date()),Month(Date())+1,0)

GROUP BY
FORMAT(CustomerDate,"yyyy-mm")


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
shane said:
I have written a query with the intent to select data from a table for the
last 12 months only and grouped by individual months.

My group by expression is:

(Year([customerdate])*12+Month([customerdate])-1)

My where expression is:

DateDiff("yyyy",[customerdate],Date())

Criterial is <=1.

Until the new year, these expressions worked together fine. Now it will
only pull data from the beginning of the most recent month. February will
all show up in April for example, but not all of March will.

I'm not sure what the fix is. Any help is appreciated.
Click to expand...
 
Back
Top