[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Need an SQL OleDb date comparison

Posted on 2007-11-22
8
Medium Priority
?
1,486 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

649 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