Compare dates in MS Access

[timdodgson]

Hi its me again lol
Ok my Problem is as follows
I have a table in an ms access db one feild is called datevisit and its type is set to date/time

So what i want to do is run a qyerry against the db from my asp page that compares against todays date by using the server varible date

strSQL = "SELECT * " _
& "FROM [sub] WHERE datevisit = # " & date & " # ;"

This is the querry string i am using it does not erroe but returns no records

Thank You in Advance
Tim Dodgson

 

[Sheco]

I find it is easiest to debug these things directly in the database and then, only after getting the syntax just right, putting it into the ASP... So go into Access and make like you are creating a new query in design mode, but then switch the SQL view and use that to test the query.

The next troubleshooting step is to do a Response.Write on your SQL variable right before you execute it... sometimes it is not what you think it will be!
[tt]Response.Write strSQL [/tt]

By doing a response.write I have found all sorts of unexpected errors from things like missing or extra spaces and also hours:mins:secs in a date where i thought it would only be month/day/year

 

[xwb]

Run the query as a vbs files in wsh and see what comes out.

 

[DotNetGnat]

make sure that your "date" variable is not empty...besides that there is nothing wrong with the query...

-DNG

 

[timdodgson]

Thanks for the replies all seems ok
the date varible does contane the date
ie response.write date prints the current date on the screen
this has me beat

What i am trying to do is an appointment outstanding page in my database so when i open page it auto tells me how many jobs i have for that day so i was going to run this querry then do a record count and that would be it as any one got any ideas on another way to do this

Tim Dodgson

 

[DotNetGnat]

did you mean...

strSQL = "SELECT *  " _
        & "FROM [sub] WHERE datevisit = # " & date() & " # ;"

strSQL = "SELECT *  " _
        & "FROM [sub] WHERE datevisit = # " & now() & " # ;"

-DNG

 

[johnwm]

Your clue lies here

one feild is called datevisit and its type is set to date/time

Strip the field to store just the date, not the time

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[timdodgson]

Thanks for the help

I fixed it with this in the end

strSQL = "SELECT * " _
& "FROM [sub] WHERE (day(datevisit)= "& visitday &" AND month(datevisit)= "& visitmonth &" AND year(datevisit)= "& visityear &")"

I have no idea y just date wpuld not work lol

Tim Dodgson