Date Problems (again!)

Dates are really doing my head in!!

I have a problem running this.

SELECT Count(tblAdvert.ModelID) FROM tblAdvert WHERE tblAdvert.ModelID = 5 AND tblAdvert.Date >= '" & NOW()-14 &"'

This should count the number of adverts in the last 14 days. I thought it was working fine until we moved onto a new month - February. It won't show any adverts in the last 14 days that are in this month.

I wrote this little script to test:

<%response.write NOW()%>
<%response.write "<BR>"%>
<%response.write NOW()-14%>
<%response.write "<BR>"%>
<% if "01/02/03 09:30:48" <= NOW()-14 then
response.write "Outside the 14 days"
else
response.write "Within the 14 days"
end if
%>

It returns the current date and time and the same 14 days ago. When I tested 01/02/03 09:30:48 - it returned "Outside the 14 days"

I have tried using the datediff function. This works when i query it in Access but doesn't for some reason when i put it in my asp page.

e.g.

SELECT Count(tblAdvert.ModelID) FROM tblAdvert WHERE tblAdvert.ModelID = 5 AND DateDiff("d",tblAdvert.Date,NOW()) <= 14;

Has anyone got any ideas to count the adverts in the last 14 days? Like i said earlier it will work ok until we hit a new month

I'm confused!:(

Try this maybe?:

SELECT Count(tblAdvert.ModelID) AS myCount FROM tblAdvert WHERE tblAdvert.ModelID = 5 AND DATEDIFF(Day,getdate(),tblAdvert.Date) <= 14

P.S. I didn't test it, but something like that should work...

whammy,

cheers for that, it now returns

"Undefined function 'getdate' in expression."

I have defined getdate by doing "Dim getdate" but it still gives the error.

Any ideas?

Oh.... that's SQL Server, not sure how to do it in Access? Now() maybe?!?