CODEWITHSUNDEEP
sqlvb.netms-access
peter_wx
peter_wx

Reputation: 91

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

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!

Upvotes: 3

Views: 1743

Answers (3)

TDolph
TDolph

Reputation: 49

You will need to use * instead of % as the wildcard and change the formatting slightly.

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

Upvotes: 0

Erik A
Erik A

Reputation: 32682

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 & "*' )"

Upvotes: 2

SandPiper
SandPiper

Reputation: 2906

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 & "*"""

Upvotes: 3

Related Questions