Recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a recordset where the user can key in a few letters in
[Forms]![frmSearchName]![Text1] and it will search for values in the field
SUMMONS.SNAME. I've done similar with an '=', but seem to be having issues
with the 'Like'. Any advise???

Thank you,
Sash


Set recset = dbSummons.OpenRecordset("SELECT SUMMONS.SNAME FROM SUMMONS
WHERE SUMMONS.SNAME Like" & [Forms]![frmSearchName]![Text1].Value & "*",
dbOpenDynaset)
 
Assuming SName is a Text field (not a Number field), you need extra quotes.
Since you also want to find it anywhere in the string, you need the wildcard
at the start as well. Something like this:

Dim strSql As String
strSql = ("SELECT SUMMONS.SNAME FROM SUMMONS " & _
"WHERE SUMMONS.SNAME Like ""*" & _
[Forms]![frmSearchName]![Text1] & "*"";"
'Debug.Print strSql
Set recset = dbSummons.OpenRecordset(strSql, dbOpenDynaset)

Hints:
====
a) Using a string makes it easier to solve. Remove the single quote from the
Debug.Print line, and when it fails you can check in the Immediate Window
(Ctrl+G) to see what went wrong.

b) If the quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

c) For an example of building a search form with lots of options, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
 
Allen,
This looks like what I need. Can't wait to get to work tomorrow and try
out! I'm such a nerd!!! Thanks a bunch!!!!
Regards,
Sasha

Allen Browne said:
Assuming SName is a Text field (not a Number field), you need extra quotes.
Since you also want to find it anywhere in the string, you need the wildcard
at the start as well. Something like this:

Dim strSql As String
strSql = ("SELECT SUMMONS.SNAME FROM SUMMONS " & _
"WHERE SUMMONS.SNAME Like ""*" & _
[Forms]![frmSearchName]![Text1] & "*"";"
'Debug.Print strSql
Set recset = dbSummons.OpenRecordset(strSql, dbOpenDynaset)

Hints:
====
a) Using a string makes it easier to solve. Remove the single quote from the
Debug.Print line, and when it fails you can check in the Immediate Window
(Ctrl+G) to see what went wrong.

b) If the quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

c) For an example of building a search form with lots of options, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sash said:
I'm trying to create a recordset where the user can key in a few letters
in
[Forms]![frmSearchName]![Text1] and it will search for values in the field
SUMMONS.SNAME. I've done similar with an '=', but seem to be having
issues
with the 'Like'. Any advise???

Thank you,
Sash


Set recset = dbSummons.OpenRecordset("SELECT SUMMONS.SNAME FROM SUMMONS
WHERE SUMMONS.SNAME Like" & [Forms]![frmSearchName]![Text1].Value & "*",
dbOpenDynaset)
Click to expand...
Click to expand...
 
Back
Top