Microsoft Query prompt

  • Thread starter Thread starter davel
  • Start date Start date
D

davel

Hi,

I'm using MS Excel and MS Query to access Oracle tables.
When I set the Criteria Field to year of date and set
Value to a specific year (e.g. 2003) the query runs fine.
When I set Value to prompt, run the query, and enter year
(e.g. 2003)at prompt I get a "Syntax error in date" msg.
The query SQL is:

SELECT MV_AHS_TRIP.TRIP_START_DATE
FROM MARVIN.MV_AHS_TRIP MV_AHS_TRIP
WHERE (TO_CHAR(TRIP_START_DATE,'YYYY')=?)

What should I enter at the prompt or what should the year
function be?

thanks

Davel
 
Don't really know the answer, but in the absence of other replies ...
would suspect the entry of 2003 being interpreted as numeric rathe
than string for comparison. Perhaps YEAR(TRIP_START_DATE) might b
better
 
Thank you for the reply. MS Query doesn't like or
recognize the "Year" function.

Am I in the right area for this type of question or is
there a better area for MS Query questions?
 
Davel

I had the same thought as Brian, but I've never seen TO_CHAR before, so I'm
not sure what it returns. If it returns a string and you are entering an
integer, maybe that's the problem. Try

WHERE (INT(TO_CHAR(TRIP_START_DATE,'YYYY'))=?)

Maybe that will cast it to an integer and will match what you enter.
 
Back
Top