?
Solved

Need an SQL OleDb date comparison

Posted on 2007-11-22
8
Medium Priority
?
1,479 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
[X]
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
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

762 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