MS Access vb code to search for string (using the LIKE) statement

Question

I have a from in Microsoft Access with a text box (name below is TextSerial). I want users to be able to enter in a string and have it display all matching records in a sub-form. Below is my code, but it is not working (no records come up).

Private Sub TextSerial_Enter()
Dim myEquipment As String
myEquipment = "Select * from tblEquipment where ([Serial] Like '% " & Me.TextSerial & " %' )"
Me.tblEquipment_subform1.Form.RecordSource = myEquipment
Me.tblEquipment_subform1.Form.Requery
End Sub

I have a feeling that it has something to do with the order of the quotations (' vs ") but I haven't been able to figure it out. I want the users to be able to only enter part of the string (which is why I am using Like instead of =). Thanks!

Answer 1

Two things I've noticed. First, you can use a double-double quote as a character escape (i.e. "He said ""Hello.""" will output the string: He said "Hello").

Second, you have a space in the query between the wildcard and your text. I don't know if that was intentional or not, but what that means is if you are searching for a string in a field, say ABC123, you would not actually get a result because the extra spaces are characters not present in that record.

See if these simple changes will fix your problem.

myEquipment = "Select * from tblEquipment where [Serial] Like ""*" & Me.TextSerial & "*"""

Answer 2

Really simple answer: Jet/ACE Like operator uses * as a wildcard, not % Adjust the following and it should work

myEquipment = "Select * from tblEquipment where ([Serial] Like '*" & Me.TextSerial & "*' )"