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

    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,
    Grace

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ??


    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 & "*') "
    endif

    if not isnull(SubIssue) then
    strSQL = strSQL & "AND (SubIssue LIKE '*" & SubIssue & "*') "
    endif

    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.



    izy
    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
  •