Multi-field Criteria for Form Filter

[utzja1]

I am working in Access 2013 and have created a form to streamline data entry for some inspections. There are hundreds of assets to be inspected, but the ID code assigned to each asset is based on codes assigned to roadway intersections, meaning that one intersection code could have up to 5 assets assigned to it. The asset code is attached as a suffix to the intersection code to create a unique ID for each asset. The record source for the form is the table with all the inspection data (here, called Table1 for simplicity).

Instead of scrolling through a combo box with all the assets to update records, I created two find-duplicate queries; one for the intersections (text field) and one for the asset codes (text field). These query results are used to populate two different combo boxes on the form. The user first chooses the intersection and then the asset ID to update. I have an event handler that fires (After Update) after the asset ID is selected. The VBA code below filters the form based on the values in both combo boxes. It should return one record for editing.


Private Sub cboAssetID_AfterUpdate()

Me.Filter = "((([Table1].Intsxn) Like " & cboIntsxn & ") AND (([Table1].AssetID) Like " & cboAssetID & "))"
Me.FilterOn = True

End Sub

If I start with only the Intersection filter, it works fine. If I add the second filter criteria, I am being asked for a parameter for the asset ID, even after entering it on the form so I can only conclude the problem is in my syntax. I tried this same type of statement when working with integer fields and had no problems.

I appreciate someone being able to point me in the right direction when using multiple text criteria to filter a form.

Thanks for your consideration!

 

[CJ_London]

try = rather than like - you only use like for strings (although you can use it for numbers) and where you are looking for part of the string. In your case you are looking for an exact match.

with regards being asked for a parameter - this implies you do not have a field called 'assetID' in Table1 - so suggest check your spelling

 

[sneuberg]

I don't think you want to use Like in this situation. Suggest trying it like I have it below and post what appears in the immediate window when you test it.

Private Sub cboAssetID_AfterUpdate()

Dim strCriteria As String
strCriteria = "[Table1].Intsxn) = " & cboIntsxn & " AND [Table1].AssetID =  " & cboAssetID
Debug.Print strCriteria
Me.Filter = strCriteria
Me.FilterOn = True

End Sub

The syntax above assumes both fields are numbers. If they are text the single quotes would be required like.

Private Sub cboAssetID_AfterUpdate()

Dim strCriteria As String
strCriteria = "[Table1].Intsxn) = '" & cboIntsxn & "' AND [Table1].AssetID =  '" & cboAssetID  & "'"
Debug.Print strCriteria
Me.Filter = strCriteria
Me.FilterOn = True

End Sub

 

[sneuberg]

Also if you use Me when creating these types of strings the intellisense will tell you if you spelling is correct, i.e, type it like:

strCriteria = "[Table1].Intsxn) = " & Me.cboIntsxn & " AND [Table1].AssetID =  " & Me.cboAssetID

and if intellisense doesn't show anything it probably means you missed a quotation mark.

 

[utzja1]

Thanks to both for the replies. Using equals instead of Like was the right approach, and using the single quote for the text field was the syntax error that I needed to correct. I used "Like" because I was referencing short text fields. The 'missing' field code exists in the database and matches it verbatim, so I'm not sure why Access thought it didn't exist.

Thanks so much!