If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query not showing all data for last month
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. |
#2
|
|||
|
|||
query not showing all data for last month
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 wrote: 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. |
#3
|
|||
|
|||
query not showing all data for last month
"shane" wrote in message ... 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. |
#4
|
|||
|
|||
query not showing all data for last month
,,bn
"shane" a écrit dans le message de groupe de discussion : ... 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. |
Thread Tools | |
Display Modes | |
|
|