Hoping I'm wording this correctly. I'm attempting to create a prompt for users that are running a procedure to enter Start and End dates to limit the results of a SQL report sent to Passthrough.

The SQL portion of the procedure runs fine with the dates hardcoded, but I wanted users to be prompted to enter their desired Start/End date range.

Added the following -SET in the procedure before the SQL Report:

-SET &FROM_DATE = &ENTER_FROM
-SET &TO_DATE = &ENTER_TO

The WHERE portion of my SQL report in the procedure is

WHERE (tablealias.date BETWEEN To_DATE(&FROM_DATE, 'DD-MM-YYYY') AND To_Date(@TO_DATE,'DD-MM-YYYY'))

I run the procedure and am prompted for the ENTER_FROM and ENTER_TO entries and enter my desired dates, but receive the following FOC error:

(FOC295) A VALUE IS MISSING FOR: &TO_DATE

I'm not certain why it is accepting the entry for the first -SET and not the second. I'm hoping I'm on the right track. Any assistance is very appreciated.

This message has been edited. Last edited by: Mike Williams,

It looks like you have a typo:

WHERE (tablealias.date BETWEEN To_DATE(&FROM_DATE, 'DD-MM-YYYY') AND To_Date(@TO_DATE,'DD-MM-YYYY'))

@TO_DATE should be &TO_DATE, but there might be more to it than just that.

Ugh, thank you for catching that. Made the change, but unfortunately receiving the same error.

Hi Mike,
Need to -DEFAULTS before the -SET:

-DEFAULTS &ENTER_FROM = '', &ENTER_TO = ''

Also, Dialogue Manager statements end in a semi-colon:
-SET &FROM_DATE = &ENTER_FROM;
-SET &TO_DATE = &ENTER_TO;

hth

And try adding this line at the top of your code:

-SET &ECHO=ALL;

Many thanks to everyone. Tom helped me to finalize the other FOC errors I was encountering with this. Solved.