No, the problem is that there are syntax errors in the query. By the way that
is ONE query and should be treated as one query.
I missed a closing parenthesis in the DateSerial function. I have
Month(Date(), and I should have Month(Date()), and now that I examine this a
little closed I note some other problems with those pesky parentheses.
Hopefully all is not correct
Also, I was not sure if you wanted the same time frame for the current month
and prior year month. For example, 1 March to 21 March in both 2009 and 2010.
I can think of one occasion where the result could be off. If the current
date was Feb 29th you will get data for Feb 1 to Mar 1 for the prior year's
month. If that is important, you will need to rewrite the criteria.
SELECT [Customer]
, SUM(IIF([SomeDate] Between DateSerial(Year(Date()),Month(Date()),1) AND
Date(),[Amount], Null)) as CurrMonth
,SUM(IIF([SomeDate] Between DateSerial(Year(Date())-1,Month(Date()),1) AND
DateSerial(Year(Date())-1,Month(Date()),Day(Date()),[Amount], Null)) as PriorMonth
,SUM(IIF([SomeDate] Between DateSerial(Year(Date()),1,1) AND
Date(),[Amount],Null)) as CurrYear
,SUM(IIF([SomeDate] Between DateSerial(Year(Date())-1,1,1) AND
DateSerial(Year(Date())-1,Month(Date()), Day(Date())),[Amount], Null)) as
PriorYear
FROM [SomeTable]
WHERE [SomeDate] Between DateSerial(Year(Date())-1,1,1) and Date()
GROUP BY Customer
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John: I do well appreciate your efforts on my behest, However, I pasted the
formuila below into a query and only the CurrMonth portion will return a
result. When I add the PriorMonth portion, it gives me an error "Syntax
Error (missing operator)". I think this is do to a conflict on the multiple
date groups. Am I supposed to create 4 separate queries and the 1 more where
I join all the information for the report. Ick!!!
Gary(dash)Mitchell(at)live(dot)com
John said:
SELECT Customer
, SUM(IIF(SomeDate Between DateSerial(Year(Date()),Month(Date()),1) AND
Date(),Amount, Null)) as CurrMonth
,SUM(IIF(SomeDate Between DateSerial(Year(Date())-1,Month(Date()),1) AND
DateSerial(Year(Date())-1,Month(Date(),Day(Date()),Amount, Null)) as PriorMonth
,SUM(IIF(SomeDate Between DateSerial(Year(Date()),1,1) AND Date()) as CurrYear
,SUM(IIF(SomeDate Between DateSerial(Year(Date())-1,1,1) AND
DateSerial(Year(Date())-1,Month(Date(), Day(Date()),Amount, Null)) as PriorYear
FROM SomeTable
WHERE SomeDate Between DateSerial(Year(Date())-1,1,1) and Date()
GROUP BY Customer
If you want the end of the month instead of the current date, you will have to
adjust the criteria used.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I need to create a YTD Report in the following format:
[quoted text clipped - 15 lines]
extract the numbers from my query and total them on the report? Please help!!
! The hair I haven't pulled out is completely gray.
