SQL query building help

[nsafreak]

Well I'm a little bit stumped on this one as the query that I built to perform this task isn't getting the proper date. Basically I'm looking to receive the number of orders processed on a weekly basis and only for the past month. So I constructed a pair of queries to accomplish this.

This query is supposed to get the data for the past 30 days (I also only want it for the current year)

SELECT ReportData.AD_DATE, *

FROM ReportData

WHERE (((ReportData.AD_DATE) Between Date() And Date()-30) AND ((Year([AD_DATE]))<>2007));

This query is supposed to give a weekly count for the orders processed but it's doing it for a longer time period, probably due to query one:

SELECT Format([AD_DATE],"w") AS Week, Count([Get Current Info].Widget_Serial_Number) AS CountOfWidget_Serial_Number

FROM [Get Current Info]

GROUP BY Format([AD_DATE],"w"), [Get Current Info].AD_DATE

So how do I get a query (or queries) to get the data that I want?

 

[WannaFly]

on your first query, date()-30 is not correct, to check just to select date()-30 and see what it returns, what you need is the DATEADD function, it's syntax is something like DATEADD(date(),'dd',-30), but check on the documentation for the system you are using

your second query, i think you want sum(serial number) not count, also instead of format try DATEPART, not sure on that one though.

 

[KLin]

http://www.blueclaw-db.com/vis...ction/vba-function.htm

The dateadd function is down the page a bit.

 

[brandonbull]

try adding this idea to the second query

where AD_DATE >= Date()-30