Query Problems for Recordset

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

I am trying to do a last name first name search for
patients using an SQL query for the recordset. On the
search form I want any part of the last name and any part
of the first name. From there I want to trim the text
entered and then do the search. This is where I am
stuck. It will not bring up what I want. I have tried
using single quotes and double quotes and the string looks
fine as far as query language goes. Any suggestions?
Thanks
Daniel
Set cn = CurrentProject.Connection
Set rs = New Recordset
strSQL = "SELECT tPt.fPtLastName, tPt.fPtFirstName
FROM tPt " & _
"WHERE (((tPt.fPtLastName)LIKE '*' & " & Trim(Me!
txtLastName) & " & '*')" & _
" AND ((tPt.fPtFirstName) LIKE '*' & " & Trim(Me!
txtFirstName) & " & '*'))"

rs.Open strSQL, cn, adOpenStatic
MsgBox rs!fPtLastName & ", " & rs!fPtFirstName
 
Hi,

Did you try msgbox(ing) your strSQL. I know this is a
practice that I've adopted and have caught many a SQL
error. Or do a debug.print and then paste that into a new
query (replacing your text box values with real values)
and see if it works.

From what it looks like, it's probably a matter of a
couple of misplaced or forgotten '. In that case, you
won't get an error because syntactically, the statement is
correct...it's a logic error.

Best Regards,
Jen
 
Back
Top