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