Wildcard question

[E]

I have an application in which the user types a number or a last name to find
a person in the database. The user clicks a Find button and the list of
people populates a list box. What I'm trying to do is add a wildcard on the
last name section so the user doesn't have to enter the full last name for
their search criteria. My code is below:

im strSQL As String
Dim lngSelect As Long
Dim strFrag As String
Dim tstlngx As Long
lngSelect = Forms!frmFind!SelectCriteria.Value
'Me![cmdAddProc].Enabled = True
Me![cmdUtilities].Enabled = True

'Build SQL string
strSQL = "select PDB.NEMC as MRN, PDB.LastName, PDB.Firstname, PDB.PatID "
strSQL = strSQL & "From PDB "

'Define values for toggle buttons
'1 = MRN
'2 = Last Name

Select Case lngSelect
Case 1:
strFrag = "where (PDB.NEMC = forms!frmFind!txtCriteria) ;"
Case 2:
strFrag = "where (((PDB.LastName) like '*" &
[Forms]![frmFind]![txtCriteria] & "'));"
'strFrag = "where (PDB.LastName = forms!frmFind!txtCriteria) ;"

End Select

strSQL = strSQL & strFrag

List30.RowSource = strSQL

The code for the criteria selection is in Case 2. The original code has
been commented out while i tried a few methods. I can fet the string to pass
the wildcard * but the list box does not populate. If I use 'Like' without
the wildcard the listbox will populate with the text that was entered in the
textbox.

Thank you for your help,

E

 

[E]

Thanks Alex. That did the trick!

Hi,
I think you need to add one more * at the end:
strFrag = "where (((PDB.LastName) like '*" &
[Forms]![frmFind]![txtCriteria] & "*'));"

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I have an application in which the user types a number or a last name to
find
a person in the database. The user clicks a Find button and the list of
people populates a list box. What I'm trying to do is add a wildcard on
the
last name section so the user doesn't have to enter the full last name for
their search criteria. My code is below:

im strSQL As String
Dim lngSelect As Long
Dim strFrag As String
Dim tstlngx As Long
lngSelect = Forms!frmFind!SelectCriteria.Value
'Me![cmdAddProc].Enabled = True
Me![cmdUtilities].Enabled = True

'Build SQL string
strSQL = "select PDB.NEMC as MRN, PDB.LastName, PDB.Firstname, PDB.PatID "
strSQL = strSQL & "From PDB "

'Define values for toggle buttons
'1 = MRN
'2 = Last Name

Select Case lngSelect
Case 1:
strFrag = "where (PDB.NEMC = forms!frmFind!txtCriteria) ;"
Case 2:
strFrag = "where (((PDB.LastName) like '*" &
[Forms]![frmFind]![txtCriteria] & "'));"
'strFrag = "where (PDB.LastName = forms!frmFind!txtCriteria) ;"

End Select

strSQL = strSQL & strFrag

List30.RowSource = strSQL

The code for the criteria selection is in Case 2. The original code has
been commented out while i tried a few methods. I can fet the string to
pass
the wildcard * but the list box does not populate. If I use 'Like'
without
the wildcard the listbox will populate with the text that was entered in
the
textbox.

Thank you for your help,

E

 

[JimBurke via AccessMonster.com]

I'm not sure if this is what you're intending, but if you put an * at the
beginning and the end of the LIKE clause it will find any name with that
string anywhere inside of it. e.g., if you have

LIKE "*ford*"

then it will find any name with 'ford' anywhere in it, so Fordson would
match, Ford would match, but so would Redford and Stanford, etc. If that's
what you want then you're good to go. If you only want names beginning with
the string then you don't want the first *.

Thanks Alex. That did the trick!

Hi,
I think you need to add one more * at the end:

[quoted text clipped - 51 lines]