multiple criteria

[shagha]

what's the problem of the code below??

I want to create a filter with more than 2 criteria


Me.FilterOn = True

Me.Filter = "[YEARS] = " & Ycode & "[years] ="&Ycode& "[months]="&Mcode& "AND[POScode] = " & Pcode


thanks a lot for your answers

 

[pbaldy]

Let's learn how to fish. This is an often-used technique to debug SQL being built in code, which is basically what a filter is.

http://www.baldyweb.com/ImmediateWindow.htm

 

[pbaldy]

Oh, and I'd reverse those lines; set the filter, then turn it on. May not matter, but I've always done it (and always seen it) reversed.

 

[nanscombe]

Deleted

 

[pbaldy]

Let's learn how to fish.

Or not I guess.

 

[nanscombe]

Well,

If you want to go fishing, who am I to stop you ...

 

[shagha]

I tried a lot but i couldn't write a filter with multiple criteria

I've written single ones like this: Me.Filter = strMcodes

but I don't know how to AND multiple filters

 

[nanscombe]

You may be closer than you think.

Take a look at SQL AND & OR Operators

Clue: you're interested in the structure of the WHERE clauses.

Or construct what you want to do as a query, in Access, then go and look at the WHERE clause in the SQL view.

 

[shagha]

I want to and the filters at the end of the code below

Option Compare Database

Private Sub Command954_Click()


    Dim strTcodes As String
    Dim strYcodes As String
    Dim strMcodes As String
    Dim strPcodes As String
    Dim strScodes As String


    Dim TlngLen As Long
    Dim YlngLen As Long
    Dim MlngLen As Long
    Dim PlngLen As Long
    Dim SlngLen As Long
    
    Const conJetDate = "\#mm\/dd\/yyyy\#"
    

    If Not IsNull(Me.Tcode & Me.Mcode & Me.Ycode & Me.Pcode) Then
    
        strTcodes = strTcodes & "([Transport code] = """ & Me.Tcode & """) AND "
        strYcodes = strYcodes & "([years] = """ & Me.Ycode & """) AND "
        strMcodes = strMcodes & "([months] = """ & Me.Mcode & """) AND "
        strPcodes = strPcodes & "([POScode] = """ & Me.Pcode & """) AND "
        strScodes = strScodes & "([SubPOScode] = """ & Me.Scode & """) AND "
        End If
  
   TlngLen = Len(strTcodes) - 5
    If TlngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No position number has entered", vbInformation, "ATtruck search form."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strTcodes = Left$(strTcodes, TlngLen)
        
        End If
        
         YlngLen = Len(strYcodes) - 5
    If YlngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No position number has entered", vbInformation, "ATtruck search form."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strYcodes = Left$(strYcodes, YlngLen)
      End If
      
         MlngLen = Len(strMcodes) - 5
    If MlngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No position number has entered", vbInformation, "ATtruck search form."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strMcodes = Left$(strMcodes, MlngLen)
        
        End If
        
         PlngLen = Len(strPcodes) - 5
    If PlngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No position number has entered", vbInformation, "ATtruck search form."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strPcodes = Left$(strPcodes, PlngLen)
        
        End If
        
     SlngLen = Len(strScodes) - 5
    If SlngLen <= 0 Then     'Nah: there was nothing in the string.
    
        MsgBox "No position number has entered", vbInformation, "ATtruck search form."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strScodes = Left$(strScodes, SlngLen)
        
     


   Me.Filter = strTcodes
   
   
   Me.Filter = strMcodes
   Me.Filter = strYcodes
   Me.Filter = strPcodes
   Me.Filter = strScodes
   

   
  Me.FilterOn = True
  
  End If
  
   
        
End Sub

 

[nanscombe]

I would come at it slightly differently. I would start with a blank string and start adding to it like this...

Private Sub Command954_Click()
 Me.Filter = buildWhereClause
 Me.FilterOn = True
End Click

Private Function buildWhereClause()
  buildWhereClause = ""

[COLOR="Red"]' Has the field got something in it?[/COLOR]
  If Len(Me.Tcode & vbNullString) > 0 Then

[COLOR="Red"]' If there is already something in the WHERE clause then add an AND[/COLOR]
    If Len(buildWhereClause & vbNullString) > 0 Then buildWhereClause = buildWhereClause & " AND "

[COLOR="red"]' Using single quotes rather than double quotes can make life easier[/COLOR]
[COLOR="red"]' when dealing with strings in SQL[/COLOR]
    buildWhereClause = buildWhereClause & "([Transport code] = '" & Me.Tcode & "')"
'   buildWhereClause = buildWhereClause & "([Transport code] = """ & Me.Tcode & """)"

  End if

  If Len(Me.Ycode & vbNullString) > 0 Then
    If Len(buildWhereClause & vbNullString) > 0 Then buildWhereClause = buildWhereClause & " AND "
    buildWhereClause = buildWhereClause & "([years] = '" & Me.Ycode & "')"
  End if

  If Len(Me.Mcode & vbNullString) > 0 Then
    If Len(buildWhereClause & vbNullString) > 0 Then buildWhereClause = buildWhereClause & " AND "
    buildWhereClause = buildWhereClause & "([months] = '" & Me.Mcode & "')"
  End if

' etc

End Function

If you start adding it one field at a time you can see the filter (WHERE clause) start to take shape.