MS Access search form query

Question

I am new to Access and am need of help with multi-text box search form in Access 2016. My form has 6 fields in which users can input data to get search result in the subform. Users would need to have the option to enter search parameters in one or more fields to get results. We have a similar form in an Access 2003 database that people love. I have tried copying and updating the code from the Access 2003 database, but I can't seem to get it to work in the 2016 database. I have spent weeks searching for answers and am at a total loss.

The below code is what I copied from the Access 2003 database and updated for the current database:

Private Sub cmdWCSearch_Click()
Dim strsql As String

strsql = "SELECT * FROM qryWCSearch WHERE ID > 0"

If Not IsNull(Me.WCLastName) Then
strsql = strsql & "And [WCLastName] Like '*" & Me.WCLastName & "*'"
End If

If Not IsNull(Me.WCDOI) Then
strsql = strsql & "And [WCDOI] Like '*" & Me.WCDOI & "*'"
End If

If Not IsNull(Me.WCWorkStatus) Then
strsql = strsql & "And [WCWorkStatus] Like '*" & Me.WCWorkStatus & "*'"
End If

If Not IsNull(Me.WCClaimNumber) Then
strsql = strsql & "And [WCClaimNumber] Like '*" & Me.WCClaimNumber & "*'"
End If

If Not IsNull(Me.WCBodyPart) Then
strsql = strsql & "And [WCBodyPart] Like '*" & Me.WCBodyPart & "*'"
End If

If Not IsNull(Me.WCClaimStatus) Then
strsql = strsql & "And [WCClaimStatus] Like '*" & Me.WCClaimStatus & "*'"
End If

End Sub

In the query I have Like "*" & [Forms]![WelcomePage]![WCLastName] & "*" Or ([Forms]![WelcomePage]![WCLastName] Is Null) under the criteria.

Whenever I run the search I get an error message saying "The expression On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control."

Any help in getting this to work would be greatly appreciated!

I NEVER use dynamic parameterized queries. I prefer to set Filter and FilterOn properties of already open form or to use WHERE argument of OpenForm and OpenReport. — June7
By using wildcards front and back of your search terms this will get painfully slow, very quickly, if you have a reasonable number of records. Using a wild card at the front of a like negates the use of any indexing. — Minty

Gustav Gustav · Accepted Answer · 2017-06-27T06:50:43

You need a space in front of all your "And"s:

If Not IsNull(Me.WCLastName) Then
    strsql = strsql & " And [WCLastName] Like '*" & Me.WCLastName & "*'"
End If

MS Access search form query

1 Answers