Clever Geek Handbook

MS vb code to find string (using LIKE statement)

I have a Microsoft Access with a textbox (name below TextSerial). I want users to be able to enter a string and display all matching records in a subform. Below is my code, but it doesn't work (no entries appear).

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 it has something to do with the order of quotations ("vs"), but I couldn't figure it out. I want users to be able to enter only part of a string (which is why I use Like instead of =). Thank!

+3


source to share


3 answers


Really simple answer: Jet / ACE How operator uses *

as wildcard rather than %

Adjust the following and it should work



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


source


Two things I've noticed. First, you can use a double double quote as an escape character (ie "He said" "Hi." "" Outputs a string:) He said "Hello"

.

Second, you have a space in your query between the wildcard and the text. I don't know if this was intentional or not, but what does it mean if you search for a string in a field, let's say ABC123

you don't actually get the result, because the extra spaces are characters missing from that entry.



See if these simple changes fix your problem.

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


source


You will need to use *

instead %

as a wildcard and change the formatting slightly.

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


source






More articles:


All Articles