Filter report

[shagha]

I have created a filter with the code below, and I want to apply in to a report, what should I do?

Private Function buildWhereClause()
  buildWhereClause = ""

' Has the field got something in it?
  If Len(Me.Tcode & vbNullString) > 0 Then

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

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


  End If
  
  If Len(Me.Tcode & vbNullString) > 0 Then
    If Len(buildWhereClause & vbNullString) > 0 Then buildWhereClause = buildWhereClause & " AND "
    buildWhereClause = buildWhereClause & "([Transport code] = '" & Me.Tcode & "')"
    
   
    
  End If

' year

' Has the field got something in it?
  If Len(Me.Ycode & vbNullString) > 0 Then

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

' Using single quotes rather than double quotes can make life easier
' when dealing with strings in SQL
    buildWhereClause = buildWhereClause & "([years] = '" & Me.Ycode & "')"

  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
    
    
   'Month
   
   ' Has the field got something in it?
  If Len(Me.Mcode & vbNullString) > 0 Then

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

' Using single quotes rather than double quotes can make life easier
' when dealing with strings in SQL
    buildWhereClause = buildWhereClause & "([Months] = '" & Me.Mcode & "')"

  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

'POScode

' Has the field got something in it?
  If Len(Me.Pcode & vbNullString) > 0 Then

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

' Using single quotes rather than double quotes can make life easier
' when dealing with strings in SQL
    buildWhereClause = buildWhereClause & "([POScode] = '" & Me.Pcode & "')"

  End If
  
  If Len(Me.Pcode & vbNullString) > 0 Then
    If Len(buildWhereClause & vbNullString) > 0 Then buildWhereClause = buildWhereClause & " AND "
    buildWhereClause = buildWhereClause & "([POScode] = '" & Me.Pcode & "')"


  End If
  
  'subPOScode
  
  ' Has the field got something in it?
  If Len(Me.Scode & vbNullString) > 0 Then

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

' Using single quotes rather than double quotes can make life easier
' when dealing with strings in SQL
    buildWhereClause = buildWhereClause & "([SubPOScode] = '" & Me.Scode & "')"

  End If
  
  If Len(Me.Scode & vbNullString) > 0 Then
    If Len(buildWhereClause & vbNullString) > 0 Then buildWhereClause = buildWhereClause & " AND "
    buildWhereClause = buildWhereClause & "([SubPOScode] = '" & Me.Scode & "')"
  
End If


End Function

 

[pbaldy]

You could use this method, with just the function in the where condition.