Comparing Dates in SQL

Hello! can anyone please help or had smiliar problems?

I have an Access database with a field named 'bid_close_date' (data type = 'Date/Time' - General Date: DD/MM/YYYY HH:MM:SS) in a table named tblplayers.

I am running the following Query, using NOW() - which I was hopeing would return all the players from tblplayers whose bid_close_date is less than NOW().

SQL = "SELECT * FROM tblPlayers WHERE bid_close_date > # " & NOW &" # ;"

HOWEVER! The SQL only seems to be looking at the first part of the Date i.e. DD and not the rest of the date string. i.e. MM/YYYY HH:MM:SS

N.B. when I compare dates in VBscript it recognises the dates and the comparisons works. i.e. return the desired result.

<%
IF Now() > rs("Bid_close_date") THEN

Response.Write("THIS AUCTION CLOSED etc...")

End If
%>

If anyone has any suggestions I would be most grateful.

David

I wonder how you can get any result at all. Now() is precise to the second and therefore, you should only be able to get a record that was inserted in the very moment of issuing the select query (or later????? beware the ">"!!!)

Second point is that you are in a MS Windows world. They are formatting dates back and forth (behind the curtain) without showing you what is going on. Access puts even more formatting to that.

Next is the question: is your bid_close_date a date/time field? I.e. does it understand dates?

Dear Friend,
At the very beginning i would like to say that there a mistake in your query.
SQL = "SELECT * FROM tblPlayers WHERE bid_close_date > # " & NOW &" # ;"


It must be some thing like

SQL = "SELECT * FROM tblPlayers WHERE bid_close_date < date()

Condition Apply
--------------------

You must go to the design view of the table tblPlayer and click on the bid_close_date field.

Down on the General Tab page you have a property called Format. Just select the Short Date from the Drop Down and save the changes to the date base.

Now u can start working
Best Wishes !!!!

Dear David,

Your Query...
SQL = "SELECT * FROM tblPlayers WHERE bid_close_date > # " & NOW &" # ;"
...is correct, nothing is wrong in that. But as you said that VB Script does the thing correctly, and not the SQL, it is bcoz, the VBScript run on the client(local) machine only and the SQL runs on the server. So the proble is the NOW function, that assumes the Server's NOW date and not your machines. So to work your query perfectly, you need your PC's system date equal to your server's system date.
Suggestion on it are always welcome.
Yogesh Patil