Need an SQL OleDb date comparison

I am having trouble doing an SQL date comparison using Access tables and OleDb.  I have been successful using an SqlDataAdapter with this type of SQL comparison below:

SELECT * FROM OrderHeaders, CustomerFiles
WHERE OrderHeaders.CustomerID=CustomerFiles.CustomerID AND
(OrderHeaders.OrderDateTime IS NOT NULL AND OrderHeaders.OrderDateTime >= '2007-11-22 08:00:00 AM')
ORDER BY OrderID ASC

but with an OleDbDataAdapter I get "Data type mismatch in criteria expression."

Does anyone know what date format (if any) will work for OldDb and Access?

Thanks,
newbieweb
newbiewebSr. Software EngineerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
GRayLConnect With a Mentor Commented:
Wrap the date with # versus '

>= #2007-11-22 08:00:00 AM#
0
 
newbiewebSr. Software EngineerAuthor Commented:
Alternatively, is there a way to use SqlDataAdapter with Access?

newbieweb
0
 
Arthur_WoodConnect With a Mentor Commented:
or:

SELECT * FROM OrderHeaders, CustomerFiles
WHERE OrderHeaders.CustomerID=CustomerFiles.CustomerID AND
(OrderHeaders.OrderDateTime IS NOT NULL AND OrderHeaders.OrderDateTime >= cDate('2007-11-22 08:00:00 AM'))
ORDER BY OrderID ASC

AW
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
newbiewebSr. Software EngineerAuthor Commented:
Thanks for your timely help.

Happy Thanksgiving!
0
 
GRayLCommented:
Had to dig for that one Arthur;-)
0
 
Arthur_WoodCommented:
not really.  I use that syntax regularly, since it is database independent.

Glad to be of assistance.

AW
0
 
GRayLCommented:
I call it robbery
0
 
Arthur_WoodCommented:
I work in VS.NET, with a multi-database environment (we have an application that can connect to ORACLE in 'normal mode', or to SQL Server in 'Training Mode'), so we are very flexible as to the use of platform specific syntax.  cDate(...) is much more general that #...# which is Access specific.  I was just offering an alternative approach for consideration.  The questioner apparently felt that it was helpful.  If you really need the points for your new Cadillac, then I will be glad to contribute all of mine (as much as they are worht oin the 'real' world), to whatever charity you would suggest.  LOL

AW
0
All Courses

From novice to tech pro — start learning today.