Query by Date not working

[DFrag]

Hello all,

I have an ASP page that refers to an Access database. Problem I am having is that, I want to access certain records that has not expired. There is a date field within the database called 'expiry'. My SQL statement looks like this:

strSQL = "SELECT * from specials WHERE expiry >" & date() & ";"

When run, it displays all records, even those that are supposed to expire earlier than todays date. Is there a trick to this that I am not seeing?

Thanks for any help
DFrag

[defanjos]

I know the expired ones will get shown like this:

strSQL = "SELECT * from specials WHERE Date()-expiry > 0 ;"

So the opposite might work:

strSQL = "SELECT * from specials WHERE Date()-expiry =< 0 ;"

Anyway, play with the above ideas - it should work.

[DFrag]

Thanks for that.

I will give it a go.

DFrag

[wackal]

here's what you do, dates in access need to be surrounded by the # sign. Rewrite your query as follows:

strSQL = "SELECT * from specials WHERE expiry > #" & date() & "#;"

[DFrag]

Wackal,

That's it! Worse part is I knew that. That's what you get when you don't code for a while!

Thanks heaps.

DFrag

[wackal]

glad to be of help!