"Where" statement - Report criteria based on combo boxes in a form

Renoir

Registered User.
Local time
Today, 21:38
Joined
May 31, 2007
Messages
42
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
 

Attachments

Renoir

I’m surprised your second option works. I’m assuming that if both option 1 and 2 are selected then both options must be met. The current code won’t do this. I think maybe you are getting the perception that it works because maybe you are choosing an asset category that only has records for the chosen employee.

Anyway, here’s the corrected code (I’ve tidied it by changing the way it deals with the trailing AND and corrected a couple of other errrors):

Code:
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
  
    ' It's optional to select from the second combo box
  
    If IsNull(Me!AssetCategoryID) = False Then
        strWhere = strWhere & " AND AssetCategoryID = " & Me!AssetCategoryID
    End If
    
    If IsNull(Me!DepartmentID) = False Then
        strWhere = strWhere & " AND DepartmentID = " & Me!DepartmentID
    End If
    
    If IsNull(Me!StatusID) = False Then
        strWhere = strWhere & " AND StatusID = " & Me!StatusID
    End If
  
    Debug.Print strWhere
    DoCmd.OpenReport "RPT_Assets", acViewPreview, , strWhere

End If

End Sub

Hth
Chris

PS it's worth removing the "security" when posting your database as some people may not be familiar with how to by-pass it and therefore cannot help.
 
Cheers Chris

I stuffed around with the “ And” statement for a long time.
Very cleaver and thank you heaps.

I take your point about the security – too focussed on the issue at hand at the cost of common sense.

Thanks again.

Renoir
 
Just a quick one, now I have my form working that compiles the report, when I open it displays an error message saying that the tblAssets is already open. This is because the button to open the report form is on the main form which also gets its data from the tblAssets. So that make sense but can I overcome this?
 

Users who are viewing this thread

Back
Top Bottom