Using VBscript and SQL on an Access database how do you select records based on a date comparison with todays date

This sounds like a very naive question but I can not get this simple requirement to work. A selection is made on one page of the website which passes a parameter time_period (which is 1, 7, 31 or 365) to the next page which should list all of the selected records where the LastUpdated date (a standard date field in Access db) is within that many days of the current date.

With single digit numbers (1 and 7) I get a result, but not 100% accurate and with 31 and 365 only very strange results. I guess there is an inconsistency in formats somewhere but I am at a loss to find a solution.

Dim my_time_period
my_time_period = Request.QueryString("time_period")
selection_list.Source = "SELECT *  FROM document WHERE DateDiff('d',LastUpdated,now) <= '"+my_time_period+"'"

I have tried hard coding a number as below but get the same results.

selection_list.Source = "SELECT *  FROM document WHERE DateDiff('d',LastUpdated, now) <= '7'"

I have also tried using Today instead of now but it gets thrown out as an error. Can anyone please help? Thank you

Answer

You’re comparing a number

DateDiff('d',LastUpdated, now)

to a string

'7'

Instead compare a number to a number:

"SELECT *  FROM document WHERE DateDiff('d',LastUpdated,now) <= "+my_time_period

Note the difference: no single quotes

Also be aware this is inviting SQL Injection.

Also be aware that in a database that uses indexes, this expression should be rewritten something like this:

SELECT *  FROM document WHERE LastUpdated > DateAdd('d',now,-"+my_time_period+")"

That way you’re comparing a column (which may be indexed) to a fixed value. It doesn’t matter so much in MS Access though