Thread: Query with optional criteria
11-22-04, 10:01 #1Registered User
- Join Date
- Mar 2004
Unanswered: Query with optional criteria
I am trying to create a query that has one required parameter (date field) and two optional parameters (Issue and SubIssue) and can't seem to get it to work right. It starts with a form that allows a user to select a date range.
Criteria for Date field is >=[Forms]![HelpDeskLogView by Criteria]![ReportStartDate] And <=[Forms]![HelpDeskLogView by Criteria]![ReportEndDate]
Next I have two additional parameters that can either be left blank or a value selected. Criteria for the Issue field is:
Like [Forms]![HelpDeskLogView by Criteria]![Issue] & "*"
and then for the SubIssue field:
Like [Forms]![HelpDeskLogView by Criteria]![SubIssue] & "*"
The problem comes in with the second SubIssue field. I can't figure out how to set up the query to allow for the date range and then TWO optional fields.
I hope I explained this well. Please let me know if you need additonal information.
11-23-04, 13:47 #2Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
code running in the form holding the criteria boxes:
dim strSQL as string
strSQL = "SELECT blah1, blah2, blah3 FROM tblBlah WHERE "
strSQL = strSQL & "((Date BETWEEN #" & ReportStartDate
strSQL = strSQL & "# AND #" & ReportEndDate & "#) "
if not isnull(Issue) then
strSQL = strSQL & "AND (Issue LIKE '*" & Issue & "*') "
if not isnull(SubIssue) then
strSQL = strSQL & "AND (SubIssue LIKE '*" & SubIssue & "*') "
strSQL = strSQL & ");"
myReport.recordsource = strSQL
docmd.openreport "myReport", acviewpreview
blah1, blah2, blah3 are the field names you want from your table tblBlah
myReport is your report name
last few thoughts:
don't have a field named "Date" - if you name fields using reserved words, confusion is certain and failure is possible.
i'm also not a fan of editable unbound textboxes having the same names as fields unless you fill them from a recordset using a For Each Field construct and intend to save edits back to the table ...so perhaps the form fields would be safer named thisIssue and thisSubIssue.
izycurrently using SS 2008R2