How to work with dates using ACCESS 2007 SQL Query

I wrote a SQL script to limit the data extracted from database as shown below but I'm unable run the query with the following error message as per attachment.


Any advice? I've also tried format and Cdate but none is able to work.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
Are you running this via a pass through query, - or perhaps ADODB and executing on SQL Server.

The syntax you use is fine in Access, if it is a pass though query, then the syntax must be that used by SQL Server.

It would be:

Looks like Oracle...

IF Kelvin's post above does not return the correct results (make sure you are not getting ship dates based on a string comparison such as 02/01/2014, 03/01/1999, 05/23/2005), try this:

select * from IFSAPP.C_SHIPMENT_ORDER_DETAILS A WHERE A.ACTUAL_SHIP_DATE > to_date('01/01/2015','mm/dd/yyyy');

Open in new window

Not sure if that will work from Access, but I think Oracle needs quote delimited date criteria explicitly converted to date format.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin SparksCommented:
I agree. and looking at the jpg, I see a reference to Oracle. The sample that mbizup has provided is correct in this instance. It would work in a pass through query or ADODB command setup, where you are passing sql to be executed at the server end. Access VBA just won't work at the server (well almost never).

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.