Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Need an SQL OleDb date comparison

Posted on 2007-11-22
8
1,452 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

860 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