Date comparison not working

[Greg]

Here is a sample of data from the purchases table of my database:

purchaseid , customerid , trackid , date , available , invoice
32 7 CGA001M 8/23/2006 0 41
33 6 CGA001M 8/25/2006 0 42
34 6 CGA002M 8/25/2006 0 42
35 7 CGA001M 8/29/2006 0 43
36 7 CGA001M 8/29/2006 1 44
37 8 CGA002M 9/6/2006 1 45
38 8 CGA001M 9/6/2006 1 45
39 8 CGA004M 9/6/2006 1 45
40 8 CGA008M 9/6/2006 1 45

I would like to construct a query that returns records for a specific
customerid where the date field is within the last 7 days. I am currently
using this query, it is created in ASP using VBScript, hence the DateAdd and
Request functions.

SELECT * FROM [purchases] WHERE [customerid] = " & Request("customerid") & "
AND [date] >= #" & DateAdd("d", -7, date()) & "# ORDER BY [date] DESC

And example output for today with customer ID 7 is:

SELECT * FROM [purchases] WHERE [customerid] = 7 AND [date] >= #30/08/2006#
ORDER BY [date] DESC

I've also tried putting the date in US format:

SELECT * FROM [purchases] WHERE [customerid] = 7 AND [date] >= #08/30/2006#
ORDER BY [date] DESC

Any suggestions?

 

[Allen Browne]

Date is a reserved word in JET SQL, and also in VBA where it stands for the
system date. Access is likely to misunderstand what you expect.

Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html
Then rename the field to PurchaseDate or similar.

If you are working in VBA, you can then use something like this:
strSql = "SELECT * FROM [purchases] WHERE ([customerid] = " & _
Request("customerid") & ") AND ([PurchaseDate] >= #" & _
Format(DateAdd("d", -7, date()), "mm\/dd\/yyyy") & _
"# ORDER BY [date] DESC

You do need to use US format for literal dates in a SQL clause.

For a list of the field names that can cause problems, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html