Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004

    Question 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.

    Thank you,

  2. #2
    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 perhaps the form fields would be safer named thisIssue and thisSubIssue.

    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts