Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44

    Question Unanswered: Multi Field Criteria (To use or not to use?)

    I want to have a form that lets you input one criteria per field in a table, but allows you the choice of using every criteria field or pick and choose.

    What I am really looking for is how to leave a field blank on the form, and make that tell the query it refers to, to no require a criteria for that search.

    I have a criteria in my query that looks like this:

    Like [Forms]![CustomerInquiry]![State] (Wants an input of a state abbrv.)

    and another that looks like this:

    > [Forms]![CustomerInquiry]![SoldYTD] (Wants a dollar amount to look above)

    Say one time, I want to search for everyone in PA but I dont care about how much they spent. Another time, I want customers who spent more that $500 but dont care where they live. How can I leave a field blank on the form and make the query understand that its criteria doesnt matter?

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Real quick and off the top of head, maybe try sumthin like this:

    Code:
    Dim StrgSQL As String, WhereStrg As String
    
    StrgSQL = "SELECT * FROM myTableName WHERE "
    WhereStrg = ""
    
    'Text Field
     If IsNull(Me.FormField1) = False Then
       WhereStrg = "TableField1='" & Me.FormField1 & "'"
    End If
    
    'Text Field
    If IsNull(Me.FormField2) = False Then
       If WhereStrg <> "" Then WhereStrg = WhereStrg & " AND "
       WhereStrg = WhereStrg & "TableField2='" & Me.FormField2 & "'"
    End If
    
    'Number Field
    If IsNull(Me.FormField3) = False Then
       If WhereStrg <> "" Then WhereStrg = WhereStrg & " AND "
       WhereStrg = WhereStrg & "TableField3=" & Me.FormField3
    End If
    
    'Date Field
    If IsNull(Me.FormField4) = False Then
       If WhereStrg <> "" Then WhereStrg = WhereStrg & " AND "
       WhereStrg = WhereStrg & "TableField4=#" & CDate(Me.FormField4) & "#"
     End If
    
    'Date Range (One Field Provided - All Records Up To specified Date)
    If IsNull(Me.FROMField) = False Then
       If WhereStrg <> "" Then WhereStrg = WhereStrg & " AND "
       WhereStrg = WhereStrg & "TableField5<=#" & CDate(Me.FROMField) & "#"
    End If
    
    'Date Range (One Field Provided - All Records Beyond specified Date)
    If IsNull(Me.TOField) = False Then
       If WhereStrg <> "" Then WhereStrg = WhereStrg & " AND "
       WhereStrg = WhereStrg & "TableField5>=#" & CDate(Me.TOField) & "#"
    End If
    
    'Date Range (Between Two Fields Provided - All Records Between and Inclusive To specified Dates)
    If IsNull(Me.FROMField) = False And IsNull(Me.TOField) = False Then
       If WhereStrg <> "" Then WhereStrg = WhereStrg & " AND "
       WhereStrg = WhereStrg & "TableField5 BETWEEN #" & CDate(Me.FROMField) & "# AND #" & Me.TOField & "#"
    End If
    
    StrgSQL = StrgSQL & WhereStrg & ";"
    Me.myFormName.RecordSource = StrgSQL
    Get the idea?

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

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