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.

select * from IFSAPP.C_SHIPMENT_ORDER_DETAILS A WHERE A.ACTUAL_SHIP_DATE > #01/01/2015#

Any advice? I've also tried format and Cdate but none is able to work.
Error-messag--SQL-Date.JPG
sltan32Asked:
Who is Participating?
 
mbizupCommented:
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.
0
 
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:
select * from IFSAPP.C_SHIPMENT_ORDER_DETAILS A WHERE A.ACTUAL_SHIP_DATE > '01/01/2015';

Kelvin
0
 
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).

Kelvin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.