If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date query parameters entered from form not working
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; |
#2
|
|||
|
|||
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. "kraymond" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update Query using parameters passed from a form | Carl | Running & Setting Up Queries | 3 | January 13th, 2005 11:54 PM |
How to get a field on a form to reflect a certain record of a query? | General Discussion | 0 | December 11th, 2004 12:56 AM | |
How do I set up a report using dates as my report header? | Robin | Setting Up & Running Reports | 16 | November 13th, 2004 01:00 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |