[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need an SQL OleDb date comparison

Posted on 2007-11-22
8
Medium Priority
?
1,495 Views
Last Modified: 2013-11-06
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
0
Comment
Question by:newbieweb
  • 3
  • 3
  • 2
8 Comments
 

Author Comment

by:newbieweb
ID: 20335652
Alternatively, is there a way to use SqlDataAdapter with Access?

newbieweb
0
 
LVL 44

Accepted Solution

by:
GRayL earned 1200 total points
ID: 20336287
Wrap the date with # versus '

>= #2007-11-22 08:00:00 AM#
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 800 total points
ID: 20336295
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Closing Comment

by:newbieweb
ID: 31410589
Thanks for your timely help.

Happy Thanksgiving!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20336331
Had to dig for that one Arthur;-)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 20336684
not really.  I use that syntax regularly, since it is database independent.

Glad to be of assistance.

AW
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20336696
I call it robbery
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 20336916
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question