Unanswered: Date Range in Parameters in a query using an expression
I want to set a date range parameter within a query.
The query needs to include all records that are live within a requested period.
The parameter fields are picked up from a table that the user enters through a form.
ReqStartDate = 01/10/07
ReqEndDate = 31/10/07
Parameters are compared against the following fields.
Record StartDate EndDate Include
1 01/09/07 31/03/08 Yes
2 25/10/07 25/10/07 Yes
3 01/09/07 30/09/07 No
4 01/11/07 31/11/07 No
Ideally what I would like to do is create an expression that will say Yes or No. And then set the criteria to Yes. Thus, filtering on only those records that would be live within 01/10/07 and 31/10/07.
Last edited by HelpMePlease; 02-12-08 at 11:35.
Reason: Add word attachment that may be easier to read
you can set parameters directly in a query, although I don't know the syntax as I don't use it.
Personally I prefer to use a form to set the parameters, and pull those results (either into the query or form/report itself).
The reason.. error checking and consistency.
using a form means you can be more specific about what parameters you supply, you can validate them to ensure data integrity and consistency, you can supply reasonable / sensible defaults.
you can refer to a value in another form using forms!<myformname>!<mycontrolname>
I am using a form to get the parameters for the reason you say. I then use the parameters form the form to select the data in the query. This is done by creating an expression in the query to see if the records fall within the parameter range.
The problem I have is that a contract (or record) that runs from 01/09/07 to 31/03/08 does not fall "between" or is not "> or =" to a start and end date of 01/10/07 and 31/10/07. So it will ignore this record although it should be included.
Generally you want to compare that the start date in the table is equal to or before the criteria end date and the end date in the table is equal to or after the criteria start date. That should catch any overlapping records.