Greetings,
I’m having some problems getting a “where” statement to work. I have a form with several combo boxes which together are (suppose) to set the filter criteria for a report.
It filters OK on the first 2 combos but when the next combo is included the report filters only on this third combo.
I stole the code here http://www.utteraccess.com/forums/access/access325547.html
All credit due to Don Mills.
I have attached a sample - the code is as follows:
Private Sub ViewReports_Click()
Dim strWhere As String
' Something must be selected from the first combo box
If IsNull(Me!EmployeeID) = True Then
MsgBox "You must select something from the first combo box"
Else
strWhere = "EmployeeID = " & Me!EmployeeID & " And "
' It's optional to select from the second combo box
If IsNull(Me!AssetCategoryID) = False Then
strWhere = "AssetCategoryID = " & Me!AssetCategoryID & " And"
End If
If IsNull(Me!DepartmentID) = False Then
strWhere = "DepartmentID = " & Me!DepartmentID & " And"
End If
If IsNull(Me!StatusID) = False Then
strWhere = "StatusID = '" & Me!StatusID & ""
End If
' Remove the extra " And " from the end of the string
strWhere = Left$(strWhere, Len(strWhere) - 4)
DoCmd.OpenReport "RPT_Assets", acViewPreview, , strWhere
End If
End Sub
Any ideas as to why filtering on EmployeeID and AssetCategoryID works OK but for DepartmentID and StatusID it only filters on these individual criteria?
Cheers,
Renoir
I’m having some problems getting a “where” statement to work. I have a form with several combo boxes which together are (suppose) to set the filter criteria for a report.
It filters OK on the first 2 combos but when the next combo is included the report filters only on this third combo.
I stole the code here http://www.utteraccess.com/forums/access/access325547.html
All credit due to Don Mills.
I have attached a sample - the code is as follows:
Private Sub ViewReports_Click()
Dim strWhere As String
' Something must be selected from the first combo box
If IsNull(Me!EmployeeID) = True Then
MsgBox "You must select something from the first combo box"
Else
strWhere = "EmployeeID = " & Me!EmployeeID & " And "
' It's optional to select from the second combo box
If IsNull(Me!AssetCategoryID) = False Then
strWhere = "AssetCategoryID = " & Me!AssetCategoryID & " And"
End If
If IsNull(Me!DepartmentID) = False Then
strWhere = "DepartmentID = " & Me!DepartmentID & " And"
End If
If IsNull(Me!StatusID) = False Then
strWhere = "StatusID = '" & Me!StatusID & ""
End If
' Remove the extra " And " from the end of the string
strWhere = Left$(strWhere, Len(strWhere) - 4)
DoCmd.OpenReport "RPT_Assets", acViewPreview, , strWhere
End If
End Sub
Any ideas as to why filtering on EmployeeID and AssetCategoryID works OK but for DepartmentID and StatusID it only filters on these individual criteria?
Cheers,
Renoir