View Full Version : Retireving data from date/time field?

Jul 8th, 2002, 06:59 PM

I have an ASP page and try to read from an Access database. I want to retrieve all records where "Date_Job" field (it is date/time field) in not "1.1.1900". This is the query I use:

sDate = CDate("1.1.1900")
sSQL = "SELECT * from employee WHERE Not Date_Job = '" & sDate & "'"
set rs = conn.execute(sSQL)

But I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

What am I doing wrong?


Jul 8th, 2002, 10:34 PM
Try this as your WHERE statement.

WHERE Date_Job != '" & sDate & "'"

Good luck.

Jul 10th, 2002, 01:59 AM
I usually use the CONVERT DATETIME() SQL function with SQL Server, I haven't tried it with Access so I don't know if it works-

Something like this that I just cooked up in Access should work for you (you can also make the date a variable, to expand on this idea):

SELECT * FROM employee WHERE employee.Date_Job <> #1/1/1900#

*Note: Dave said this already pretty much, I just thought that since the post above his contained an incorrect "not equals" sign for SQL I would try to clarify the situation a bit.