SQL with Where condition and like

[jcright]

Hello all,

I'm struggling to figure out how to create an SQL statement using where and like that checks a serial number entered on a form and returns certain info.

Set dbQuick = CurrentDb
Set rsQuick = dbQuick.OpenRecordset("SELECT Comms2.DateofComm, Comms2.LastName, " & _
"Comms2.SerialNumber, Comms2.SP1 FROM Comms2 " & _
"WHERE Comms2.SerialNumber='" & Me.SerialNumberTextBox & "';")

While the code works, it only works if I have an exact serial number match. I want change it so that I get a close approximation. So if my recorded serial number is A54167 but I enter in 54167, then I get a match for that record. If my serial number is ever off, then it is usually that first character or it was never sold. I had thought that if I change the last line, then it should work. Btw, Me.SerialNumberTextBox refers to the text box on my form and that is the only fill-able textbox.

"WHERE Comms2.SerialNumber Like '*' & Me.SerialNumberTextBox & '*' & "';")
When I substitute that code in it tells me that I have a list separator problem. So I know I have some sort of problem with my quotes, maybe something else? I know this has to be an easy fix, but I can't figure out what that fix is.



Thanks,
John

[Minty]

Debugging tip number 1 - put your SQL strings into a variable and debug.print it :

Dim strSQL as String

Set dbQuick = CurrentDb

strSQL = "SELECT Comms2.DateofComm, Comms2.LastName, " & _
"Comms2.SerialNumber, Comms2.SP1 FROM Comms2 " & _
"WHERE Comms2.SerialNumber Like '*' & Me.SerialNumberTextBox & '*' & "';")
Debug.Print strSQL

Set rsQuick = dbQuick.OpenRecordset(strSQL)

This will show you exactly where your concatenation isn't quite right in the immediate window.

If you are still stuck come back with a copy of the immediate window text.

[Micron]

You're doing this in vba? Might be best if you posted the whole statement. Looks to me like you're missing ending quote - after the )" but also before the first '
You don't need ; in vba constructed code. In you example, you don't need to separate it if you do use it: ... Me.SerialNumberTextBox & "'*'; )"

I had to insert an extra space otherwise I get an emoticon.