Sales last month

[sillysod]

SELECT Format([Order Recieved],"mmmm yyyy") AS [Month], Sum([Sales Orders].Value) AS SumOfValue, Count([Sales Orders].[G Number]) AS [CountOfG Number]
FROM [Sales Orders]
GROUP BY Format([Order Recieved],"mmmm yyyy")
HAVING (((Format([Order Recieved],"mmmm yyyy"))=DateAdd("m",-1,Format(Now(),"mmmm yyyy"))));


Hi, im using the above sql query to try and get a total for sales last month and how many sales.


I can get sales THIS month without any probs, but last month just returns blank records

 

[dhookom]

Try move the Format() outside the DateAdd() and change the Having to a Where.

SELECT Format([Order Recieved],"mmmm yyyy") AS [Month],
Sum([Sales Orders].Value) AS SumOfValue,
Count([Sales Orders].[G Number]) AS [CountOfG Number]
FROM [Sales Orders]
WHERE Format([Order Recieved],"mmmm yyyy")=Format(DateAdd("m",-1,Date()),"mmmm yyyy");
GROUP BY Format([Order Recieved],"mmmm yyyy");



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sillysod]

superb, thanks alot