filter results to display those older than 'today'?

[supasnail]

I've been going round in circles here trying to get this to work and my
ignorance of ASP is now showing me up.

I want to construct a query string to allow me to filter entries to be
displayed on a web page with respect to the date-entry contained in the
database.

i.e. an archived results page will show all entries where "my_date" is
less than 'todays date'

I have tried to do this by;

Dim strSQL
Dim showdate

showdate = Date

then...

strSQL = "Select * FROM my_database WHERE my_date <" & showdate

Although this does give a result, it has no actual bearing on todays
date and wondered what strinkingly obvious this I'm doing wrong? when I
use > i get all results displayed, when i use < I get none at all.

thanks for any help

 

[Ray Costanzo [MVP]]

Please tell us what kind of database you're using.

Ray at work

 

[supasnail]

sorry, its an access database .mdb

S

Please tell us what kind of database you're using.

Ray at work

 

[Ray Costanzo [MVP]]

Format the date accordingly then, YYYY-MM-DD. See here:
http://www.aspfaq.com/show.asp?id=2023

Sample:

Function dtForAccess(d)
dtForAccess = Year(d) & "-" & Right("0" & Month(d), 2) & "-" & Right("0"
& Day(d), 2)
End Function

Ray at work

 

[supasnail]

I don't know if it makes any difference but I have the database field
set to date/time, and "short Date" and am using a
<%session.lcid=1046%> on the results page ( I know this is really for
Portugal but the 2057 for UK never displays as UK date format should,
not for me anyway.)
The Access database can be written to and read from happily and
displays the date correctly. I just dont seem to be able to work out
how to filter with respect to todays date.

S



An Access

 

[Bob Barrows [MVP]]

I've been going round in circles here trying to get this to work and
my ignorance of ASP is now showing me up.

I want to construct a query string to allow me to filter entries to be
displayed on a web page with respect to the date-entry contained in
the database.

i.e. an archived results page will show all entries where "my_date" is
less than 'todays date'

I have tried to do this by;

Dim strSQL
Dim showdate

showdate = Date

then...

strSQL = "Select * FROM my_database WHERE my_date <" & showdate

Although this does give a result, it has no actual bearing on todays
date and wondered what strinkingly obvious this I'm doing wrong? when
I use > i get all results displayed, when i use < I get none at all.

thanks for any help

If you want today's date, then use the Date() function:

strSQL = "... WHERE my_date < Date"

 

[supasnail]

Thanks Bob, thats perfect - I was on the right lines but you've allowed
me to put everything in the right place lol. Now,... on to my next
headache lol

thank you very much

S