Darn Dates Again

[christrinder]

Hello,

I'm trying to include a before and after date function into one of my search pages, but it always returns nothing now. The two form inputs are passing through the values in the format dd/mm/yy and the date stored in the DB is the now() so it looks like dd/mm/yy hh/mm/ss. Am I doing something wrong in my SQL statement below?

...JobRequest.JobDate BETWEEN #"& request.form("Date1") &"# AND #"& request.form("Date2") &"# ...

Thanks,
Chris

[allida77]

First do a Response.Write(sql) to be sure the correct values are being compared in the sql. Then try doing a VarType() (http://www.devguru.com/Technologies/vbscript/quickref/vartypecon.html) to be sure your fields are a date type. If it is not do a CDate() to convert it. You probaly just need to put the dates into the proper date format for whatever db you are using.

[christrinder]

I finally solved it. For anybody else struggling with crappy access. It doesn't matter what format your date looks like it is inside the database, the SQL statement on the page treats it in the format mm/dd/yy. ... might just help somebody else keep more of their hair than I have!

[raf]

Sorry i wasn't around cause i've could have saved you the hairs. (actually, a search could also have done that
http://www.codingforums.com/showthread.php?s=&threadid=18871&highlight=date

I gave up on the dates struggle and i just save them in mm/dd/yyyy and use
session.lcid
or
longdate to display them