query problem - access and oracle linked tables 1

[maureenmooney]

hi,
i have ACCESS tables that are linked to oracle database and the queries with dates will not execute. i put hard brackets around the date in the sql script, yet a pop up window still asks for the date when i run the query:

SELECT SURGERY.PTID, SURGERY.PTINIT, SURGERY.SURGDATE
FROM SURGERY
WHERE (((SURGERY.SURGDATE)<[#8/1/2000#]))
ORDER BY SURGERY.PTID;

obviously, i want the query to run withouth asking for the date. can anyone help??

thanks!

 

[malleykr]

Try this instead:

SELECT SURGERY.PTID, SURGERY.PTINIT, SURGERY.SURGDATE
FROM SURGERY
WHERE SURGERY.SURGDATE<DateValue('8/1/2000')
ORDER BY SURGERY.PTID;

 

[cmmrfrds]

The # is an Access specific convention to denote a date literal. It also uses the &quot; to denote a text literal while most other databases use the ' to denote any type of literal.

I am not sure about Oracle but I would try the single quote.
< '8/1/02'

 

[maureenmooney]

thank you malleykr,
you rock!