query to find recordset for list box

[terre]

The code

strFilter = "SELECT Contacts.ID, Contacts.[First Name], Contacts.[Last Name], [First Name] & ' ' & [Last Name] "
strFilter = strFilter & "AS CName, Contacts.IDate, DateDiff('d',[IDate],Now()) FROM Contacts "
strFilter = strFilter & "WHERE " & (((DateDiff("d", [Contacts.IDate], Now())) < 7))
strFilter = strFilter & " ORDER BY Contacts.[Last Name];"

gives an error message saying ..." False ORDER BY Contacts.[Last Name]"

The query whose sql is
SELECT Contacts.ID, Contacts.[First Name], Contacts.[Last Name], [First Name] & ' ' & [Last Name] AS CName, Contacts.IDate, DateDiff("d",[IDate],Now())
FROM Contacts
WHERE (((DateDiff("d",[IDate],Now()))<7))
ORDER BY Contacts.[Last Name];

Works fine.

Maybe I,m too close...can anyone spot whats wrong?
I want to select the Names where IDate is within the last week.

Terre

 

[dhookom]

I think you want the datediff inside the quotes:

strFilter = "SELECT ID, [First Name], [Last Name], [First Name] & ' ' & [Last Name] "
 strFilter = strFilter & "AS CName, IDate, DateDiff('d',[IDate],Now()) FROM Contacts "
 strFilter = strFilter & "WHERE DateDiff('d', [IDate], Now()) < 7 "
 strFilter = strFilter & " ORDER BY [Last Name];"

Duane
Hook'D on Access
MS Access MVP

 

[terre]

Yay!
Thanks
Have a great Xmas!

Terre