For simplicity I am only working with the code for handling the date at the moment and below is what I am trying to use. If the user leaves both :After_Date and :Before_Date blank the query returns all records in the default range. If the user inputs a date for either or both variables the code will work as long as the user inputs in the format YYYY-MM-DD but MM/DD/YY causes an error. If I remove the CASE statement, and thus the ability to supply a default value, forcing the user to enter a date in each parameter then any OpenOffice date syntax will work.
Aparently the CASE statement is insulating the parameter popup from seeing that the parameter is destined for use as date type and changing the way it is handled. If I press the TAB key after entering the date string in the popup WITHOUT the CASE statement the input expanded to #MM/DD/YYYY# right in the popup text box regardless of the format the user entered the date (YY-MM-DD or DD/MM/YY or any other format all return #MM/DD/YYYY#). However WITH the CASE statement the user input is simply single quoted exactly as input. I realize that date format is very country dependent but that is why I anticipate many errors if I force the use of YYYY-MM-DD in this country.
The popup seems to know how to look at the SQL code and decide how to format the user input accordingly but the CASE is confusing this mechanism. Is there a way for ME to tell the popup to handle the user input as a date so that I can use the CASE statement?
This works only with date input in the format YYYY-MM-DD or with no input by using a coded default
Code: Select all
SELECT "Company", "City", "Position", "Date"
FROM "Search_Record"
WHERE (
"Date" BETWEEN CASE WHEN :After_Date = '' THEN '1999-01-01' ELSE :After_Date END AND CASE WHEN :Before_Date = '' THEN '2999-01-01' ELSE :Before_Date END
)
Code: Select all
SELECT "Company", "City", "Position", "Date"
FROM "Search_Record"
WHERE (
"Date" BETWEEN :After_Date AND :Before_Date
)