Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Toronto, Ontario, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple query criteria (XP)

    Hi everyone,
    I have a query that uses another query for a record source and I need to have criteria sent to the first (or base query). The criteria field in base query currently gets its data from a form (Forms![sfrmDateEntry]![txtTranType] ) It work if I only use a single criteria ( TranType = "ATM" ) but I need the criteria to be ( TranType = "ATM" or TranType = "Cash" ) or posibly more than two types. If I open the base query in design mode and type in the criteria field ("ATM" Or "Cash" Or "CC") it works; other wise it does not, even if the (Forms![sfrmDateEntry]![txtTranType] ) = ("ATM" Or "Cash" Or "CC") . Basically I want to use multiple criteria for one or more fields without having the user type directly into the query. Any ideas?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Multiple query criteria (XP)

    Note: Link to "Mocrosoft" didn't work - typo <img src=/S/frown.gif border=0 alt=frown width=15 height=15> - corrected link:

    ACC2000: How to Create a Parameter In() Statement

    I haven't tried technique described in article, didn't think you could use parameters with "In". For this type of thing I usually generate SQL string in VBA, using multi-select listboxes to allow user to select multiple criteria for each field. Example of generalized sub used for this:

    Sub GetSQLString()

    Dim strSQL As String
    Dim strIN As String
    Dim strSelect As String
    Dim strGroupBy As String
    Dim strWhere As String
    Dim strOrderBy As String

    ' Other stuff here
    ' ------------------------------------------------------------------------------------
    ' Generate WHERE clause:
    ' ------------------------------------------------------------------------------------
    ' Field1 is numeric:
    strIN = GetInString(Me.Listbox1, 1)
    If Len(strIN) > 0 Then
    strWhere = strWhere & "Field1 In(" & strIN & ") AND "
    End If

    ' Field2 is text:
    strIN = GetInString(Me.Listbox2, 2)
    If Len(strIN) > 0 Then
    strWhere = strWhere & "Field2 In(" & strIN & ") AND "
    End If

    ' Field3 is date:
    strIN = GetInString(Me.Listbox3, 3)
    If Len(strIN) > 0 Then
    strWhere = strWhere & "Field3 In(" & strIN & ") AND "
    End If

    If Len(strWhere) > 0 Then
    ' Add "Where" & get rid of " AND " at end:
    strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 5)
    Else
    strWhere = ""
    End If
    ' ------------------------------------------------------------------------------------
    strSQL = strSelect & " " & strWhere & " " & strGroupBy & " " & strOrderBy & ";"
    ' Other stuff

    End Sub

    Above shows that part of sub used to generate WHERE clause for the SQL statement. The function below is used to get selected items from a multi-select listbox for IN string:

    Private Function GetInString(ByRef ctl As ListBox, _
    ByRef intDataType As Integer) As String

    Dim varItem As Variant
    Dim strItems As String
    ' intDataType 1 = numeric, 2 = text, 3 = date

    With ctl
    ' varItem = row index
    For Each varItem In .ItemsSelected
    Select Case intDataType
    Case 1 'numeric
    strItems = strItems & .ItemData(varItem) & ","
    Case 2 'text
    strItems = strItems & "'" & .ItemData(varItem) & "',"
    Case 3 'date
    strItems = strItems & "#" & .ItemData(varItem) & "#,"
    End Select
    Next varItem
    End With

    If Len(strItems) > 0 Then
    GetInString = Left(strItems, Len(strItems) - 1)
    Else
    GetInString = vbNullString
    End If

    End Function

    Note inclusion or exclusion of delimiters based on data type. The SQL generated by these functions is used to dynamically change the form's RecordSource property based on user's selected options.

    HTH

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple query criteria (XP)

    <P ID="edit" class=small>(Edited by HansV on 28-Aug-03 11:24. Corrected typo in URL - thanks to MarkD for pointing this out.)</P>The "neat" way to handle this in a query itself is to use In: set the Criteria line to In ("ATM","Cash", "CC"). You can't do this directly in a parameter query. See ACC2000: How to Create a Parameter In() Statement for two methods to do this; both methods should work if you use a parameter from a form, I think.

    Alternatively, you could use code to assemble an SQL string.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple query criteria (XP)

    Mark,

    Thank you for spotting and correcting my typo. I have corrected my reply too.

    I often use the technique you describe, to generate SQL dynamically. Very flexible.

    One note for non-US users: SQL requires US date format mm/dd/yy (or mm/dd/yyyy), so if you use a different default date format (such as mm-dd-yyyy or yyyy.mm.dd), you must format the dates. In the GetInString function:

    Case 3 'date
    strItems = strItems & "#" & Format(.ItemData(varItem), "mm/dd/yy") & "#,"

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Multiple query criteria (XP)

    That's a good point. Even with US users only, I often find it necessary to format date fields as text (in mm/dd/yyyy format) using Format function, for example, when using "Like" with date field in query. If you don't explicitly specify date format, query may or may not return correct results, depending on user's Windows Control Panel Regional Settings for Date/Time. So to avoid issues with different user settings, it's a good idea to specify date format.

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Location
    Toronto, Ontario, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple query criteria (XP)

    Thanks Hans for the email on the MS KB article on using the Instr() function in a query to handle multipe criteria. It worked like a charm. I wish I had asked the question earlier because I was spending lots of time with no results. So thanks again, it will come in handy with many other projects i will be working on.

    Jonathan Patenaude

Posting Permissions

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