Microsoft Query help - passing date as a parameter

[Guest]

Hi,

I'm having a bit of an infuriating problem which I'm hoping someone will be
able to help with....

I'm using Microsoft Query to dump data from an Oracle db into an Excel
spreadsheet, and need to pass a date from a cell so that the data returned is
from 'less than or equal' to this date. It works fine when I hard-code the
date into the query, but if I have it as a user entered parameter it always
shows up an error.

Any idea what I need to do? Is there a way to force the # # symbols around
the parameter so the format is converted?

Any help would be hugely appreciated!

Here's the query with the date hard-coded in:

SELECT v.VOUCHER_TYPE, v.VOUCHER_NO, v.VOUCHER_DATE, v.ACCOUNT,
v.ACCOUNT_DESC, v.CODE_B, v.AMOUNT, v.TEXT
FROM IFSAPP.PROJECT_ACTIVITY a, IFSAPP.GEN_LED_VOUCHER_ROW_UNION_QRY v
WHERE v.PROJECT_ACTIVITY_ID = a.ACTIVITY_SEQ AND v.CODE_C = a.PROJECT_ID AND
((v.CODE_C=?) AND (a.SUB_PROJECT_ID Like 'UC') AND (a.ACTIVITY_NO Like '00')
AND (v.VOUCHER_DATE<={ts '2007-10-26 00:00:00'}))



Marc

 

[Guest]

Try this --
AND (v.VOUCHER_DATE<= CVDate([Enter date - 11/3/2007])))

I see other things I question ---
AND ((v.CODE_C=?) -------- if

 contains '?' as text then it needs to
have double quotes.
AND (a.SUB_PROJECT_ID Like 'UC') ----- normally I see LIKE used with
wildcards.
AND (a.ACTIVITY_NO Like '00') ----- normally I see LIKE used with wildcards.

 

[Guest]

Thanks for the reply.

I tried your code below, but MS Query throws an 'invalid identifier' error
sadly (not the first simple difference I've seen compared to Access). Not to
worry though, I managed to find a way around the problem. It seems Oracle
recognises the dates in the format 26/oct/07 fine, so I've used your tip and
put an example of the date format in the prompt...

I'll go back and change 'like' to '=' for those two conditions (I'm pretty
sure the query wizard put those there!)... the CODE_C part is actually just a
badly named in one of the tables ('project_ID' would be far easier), so the
'?' is prompting for input.

Marc

Try this --
AND (v.VOUCHER_DATE<= CVDate([Enter date - 11/3/2007])))

I see other things I question ---
AND ((v.CODE_C=?) -------- if

 contains '?' as text then it needs to
have double quotes.
AND (a.SUB_PROJECT_ID Like 'UC') ----- normally I see LIKE used with
wildcards.
AND (a.ACTIVITY_NO Like '00') ----- normally I see LIKE used with wildcards.


--
KARL DEWEY
Build a little - Test a little


[QUOTE="Marc T"]
Hi,

I'm having a bit of an infuriating problem which I'm hoping someone will be
able to help with....

I'm using Microsoft Query to dump data from an Oracle db into an Excel
spreadsheet, and need to pass a date from a cell so that the data returned is
from 'less than or equal' to this date. It works fine when I hard-code the
date into the query, but if I have it as a user entered parameter it always
shows up an error.

Any idea what I need to do? Is there a way to force the # # symbols around
the parameter so the format is converted?

Any help would be hugely appreciated!

Here's the query with the date hard-coded in:

SELECT v.VOUCHER_TYPE, v.VOUCHER_NO, v.VOUCHER_DATE, v.ACCOUNT,
v.ACCOUNT_DESC, v.CODE_B, v.AMOUNT, v.TEXT
FROM IFSAPP.PROJECT_ACTIVITY a, IFSAPP.GEN_LED_VOUCHER_ROW_UNION_QRY v
WHERE v.PROJECT_ACTIVITY_ID = a.ACTIVITY_SEQ AND v.CODE_C = a.PROJECT_ID AND
((v.CODE_C=?) AND (a.SUB_PROJECT_ID Like 'UC') AND (a.ACTIVITY_NO Like '00')
AND (v.VOUCHER_DATE<={ts '2007-10-26 00:00:00'}))



Marc[/QUOTE][/QUOTE]