Date Comparison

[Dino]

I am creating a website that is going to ask the user to enter a date! then
from an access database get all records where a date field is greater then
the date entered! Sounds simple, I do it in vb all the time!

here is my sql line: Select * From Participants WHERE DateJoined > 01/14/07
ORDER BY TournamentID

it updates fine with the date the user has entered! I know in vb I need to
put ## around the date I am comparing! I tried that, I tried %%. still I
am getting all records from the database!

any and all help is appreciated!
thank you in advance.

mike

 

[Bob Barrows [MVP]]

I am creating a website that is going to ask the user to enter a
date! then from an access database get all records where a date
field is greater then the date entered! Sounds simple, I do it in vb
all the time!

here is my sql line: Select *
http://www.aspfaq.com/show.asp?id=2096


From Participants WHERE DateJoined >
01/14/07 ORDER BY TournamentID

it updates fine with the date the user has entered! I know in vb I
need to put ## around the date I am comparing! I tried that, I tried
%%. still I am getting all records from the database!

It's ##. Why would you try %%? That's for strings, not dates, and it's not
even a delimiter. It's a wildcard. Of course, if you used parameters, you
would not have to worry about these silly delimiter issues.
(http://www.google.com/[email protected]&oe=UTF-8&output=gplain
)

Why are you using an ambiguous format for your date? Always pass dates in
yyyymmdd format. For Access, you will need to use #yyyy-mm-dd#

http://www.aspfaq.com/show.asp?id=2040 help with dates
http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion
http://www.aspfaq.com/show.asp?id=2313 vbscript

The only way to debug a sql statement is to find out what the statement is
that you are passing to the database. The only way to determine that with
Access is to Response.Write it, like this:

dim dDate, sSQL
dDate=<wherever you are getting the date from>
sSQL = Select * From Participants WHERE DateJoined > #" & _
dDate & "# ORDER BY TournamentID"

'*****for debugging - comment these lines when finished***
Response.Write sSQL
Response.End
'******************************************************************

Run the page. If you've created the sal statement correctly, you should be
able to open your database in Access, create a new query in design view
without selecting any tables, switch to SQL View, paste in the statement
that appears in the browser window and run it without modification (that is
your goal when creating dynamic sql: create a statement that will run
without modification in the query execution tool provided by your database.)

The above should be done before you open a connection to your database.

Bob Barrows

 

[Bob Barrows [MVP]]

I am creating a website that is going to ask the user to enter a
date! then from an access database get all records where a date
field is greater then the date entered!

..
There was no need to crosspost this here. Your question is definitely
database-related, and most of us subscribe to both of these asp newsgroups,
so you did not increase your chances of getting an answer.

 

[Dino]

I wish to take this time to thank you Bob, for you valuable input. your
ideas and comments will be useful as I move forward! thank You