Filtering Dates - Last XX days?

[DBLoser]

Here's my query (a weekly group query):

SELECT DISTINCTROW Format$([qry_DMR_main].[DATEOPEN],'yyyy mm dd') AS [DATEOPEN By Week], qry_DMR_main.DMRNUMBER, Count(*) AS [Count Of qry_DMR_main]
FROM qry_DMR_main
GROUP BY Format$([qry_DMR_main].[DATEOPEN],'yyyy mm dd'), qry_DMR_main.DMRNUMBER, Year([qry_DMR_main].[DATEOPEN])*7+DatePart('d',[qry_DMR_main].[DATEOPEN])-1;

I changed the date formatting to start with Year because the normal sort sorts by month 01 and each consecutive day and year and then all the 02s, etc. What I need to do is modify this query to include all data from say Today - 30 days, so I can get the last months records. I tried using the between date criterias but it doesn't seem to work with the way my dates are formatted starting with year. Maybe there is a better way to sort, I don't know. Here's a sample data set.

01/01/02
01/10/03
04/15/04
01/01/04
03/03/02
12/20/03

When it is sorted ascending I get:

01/01/02
01/10/03
01/01/04
03/03/02
04/15/04
12/20/03

It is incorrectly sorting by the month. I need it by the year. But if my workaround is fine then I just need help with a criteria that will show the last thirty days.

Thanks for any help!

Bruce

 

[PHV]

Something like this ?
SELECT Format(DATEOPEN,'yyyy mm dd') AS [DATEOPEN By Week], DMRNUMBER, Count(*) AS [Count Of qry_DMR_main]
FROM qry_DMR_main
WHERE Int(Date() - DATEOPEN) <= 30
GROUP BY Format(DATEOPEN,'yyyy mm dd') AS [DATEOPEN By Week], DMRNUMBER;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DBLoser]

Thanks for the query. I used the...
WHERE Int(Date() - DATEOPEN) <= 30
...statement and it worked great.

I copied and pasted your code verbatim to utilize the "By Week" instead of the bulky code I am using but I got an error when I tried to run the query:

Syntax error (missing operator) in query expression 'Format(DATEOPEN, 'yyyy mm dd') AS [DATEOPEN By Week]'.

And then the cursor highlights AS. I don't know what the error is telling me but by just inserting the WHERE statement it solved my problem.

Thanks Again!