Date query parameters entered from form not working

[kraymond]

In an OBDC database, I have a query that I need to limit with three
parameters, SiteName, StartDate, and EndDate (date fields in "Between...And"
expression). I have an unbound form where these three values can be entered.
The query will not return any records when the date parameters are entered
in the form and referenced in the query criteria. The entry form is open. I
have set the format for the Forms!Form1!txtStartDate and txtEndDate
parameters to Date/Time. Thanks for your help.

Here are the steps I've tried (by "query worked", I mean some records were
returned):
1. Query worked when only SiteName had a form-based parameter.
2. Query worked when the query date field had the criteria "Between [Start
Date] And [End Date]" and I entered the dates in the input boxes that popped
up when the query runs.
3. Query failed when date field criteria was [Forms]![Form1]![txtStartDate]
(no records returned)
4. Query failed when criteria was Between Forms!Form1!txtStartDate AND
Forms!Form1!txtEndDate
5. Query failed when date field criteria was Between
Format([Forms]![Form1]![txtStartDate],"#""mm/dd/yyyy""#") And
Format([Forms]![Form1]![txtEndDate],"#""mm/dd/yyyy""#") Error box says that
it is too complex.

Here is the SQL of the query:
PARAMETERS [Forms]![Form1]![txtStartDate] DateTime,
[Forms]![Form1]![txtEndDate] DateTime;
SELECT DeviceInfo.SiteName, DeviceInfo.DeviceName,
WaterLevelData.CorrectedWL, WaterLevelData.MeasurementDate
FROM (SiteInfo INNER JOIN DeviceInfo ON SiteInfo.SiteName =
DeviceInfo.SiteName) INNER JOIN WaterLevelData ON DeviceInfo.DeviceName =
WaterLevelData.DeviceName
WHERE (((DeviceInfo.SiteName)=[Forms]![Form1]![lboSiteName]) AND
((WaterLevelData.CorrectedWL) Is Not Null) AND
((WaterLevelData.MeasurementDate) Between [Forms]![Form1]![txtStartDate] And
[Forms]![Form1]![txtEndDate]))
ORDER BY DeviceInfo.DeviceName, WaterLevelData.MeasurementDate;

 

[kraymond]

I made it work in a non-ODBC database and then went back and changed the tab
order on my input form to make the cmdRunQuery button run last. This fixed
it, allowing me to use "between Forms!Form1!txtStartDate And
Forms!Form1!txtEndDate" as my criteria.