Solved

Need an SQL OleDb date comparison

Posted on 2007-11-22
8
1,444 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 300 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 200 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

770 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