Wild Card Search Question

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

Guest

Hi all,
I have a search form that allows the user to look up records based on
multiple fields. However, I am wondering if I can set up some type of
wildcard search, so that if the first couple letters are entered, it will
still bring up all matching records. For example, if I was looking for the
last name "Smith" but only entered "Smi" it would still bring in the "Smith"
records. However, I would only want to apply this for text searches, not for
phone number or zip code. Here is the code that I currently have:
Private Sub Command6_Click()
Dim strWhere As String
strWhere = "True"
If Not IsNull(Me.LastName) Then
strWhere = strWhere & " AND ShipTo_LName=""" & Me.LastName & """"
End If
If Not IsNull(Me.ZipCode) Then
strWhere = strWhere & " And ShipTo_ZIP5='" & Me.ZipCode & "'"
End If
If Not IsNull(Me.PhoneNum) Then
strWhere = strWhere & " And ShipTo_Phone='" & Me.PhoneNum & "'"
End If
If Not IsNull(Me.Email) Then
strWhere = strWhere & " And ShipTo_Email='" & Me.Email & "'"
End If
If Not IsNull(Me.OrderNum) Then
strWhere = strWhere & " And Order_ID=" & Me.OrderNum
End If
DoCmd.OpenForm "INQUIRY_MAIN_View", , , strWhere
DoCmd.Close acForm, "INQUIRY_MAIN"
End Sub

Also, what could I add to this code so that if there are no matching
records, I could have a message pop up that says there are no matching
records? Currently I get an error message Runtime 2427 (no matching value)
Thank you for your help!
-gary
 
Hi Gary

Try the following...

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

Dim strWhere As String
strWhere = "True"
If Not IsNull(Me.LastName) Then
strWhere = strWhere & " AND ShipTo_LName like """ & Me.LastName & "*"""
End If
If Not IsNull(Me.ZipCode) Then
strWhere = strWhere & " And ShipTo_ZIP5='" & Me.ZipCode & "'"
End If
If Not IsNull(Me.PhoneNum) Then
strWhere = strWhere & " And ShipTo_Phone='" & Me.PhoneNum & "'"
End If
If Not IsNull(Me.Email) Then
strWhere = strWhere & " And ShipTo_Email like '" & Me.Email & "*'"
End If
If Not IsNull(Me.OrderNum) Then
strWhere = strWhere & " And Order_ID=" & Me.OrderNum
End If
DoCmd.OpenForm "INQUIRY_MAIN_View", , , strWhere
DoCmd.Close acForm, "INQUIRY_MAIN"

Exit_Command6_Click:
exit sub

Err_Command6_Click:
if err.number=2427 then
msgbox "No Matching Records"
resume next
else
msgbox "Error: " & err.number & vbcr & _
"Desc: " & err.description
resume Exit_Command6_Click

End Sub


Regards

Andy Hull
 
Gary said:
I have a search form that allows the user to look up records based on
multiple fields. However, I am wondering if I can set up some type of
wildcard search, so that if the first couple letters are entered, it will
still bring up all matching records. For example, if I was looking for the
last name "Smith" but only entered "Smi" it would still bring in the "Smith"
records. However, I would only want to apply this for text searches, not for
phone number or zip code. Here is the code that I currently have:
Private Sub Command6_Click()
Dim strWhere As String
strWhere = "True"
If Not IsNull(Me.LastName) Then
strWhere = strWhere & " AND ShipTo_LName=""" & Me.LastName & """"
End If
If Not IsNull(Me.ZipCode) Then
strWhere = strWhere & " And ShipTo_ZIP5='" & Me.ZipCode & "'"
End If
If Not IsNull(Me.PhoneNum) Then
strWhere = strWhere & " And ShipTo_Phone='" & Me.PhoneNum & "'"
End If
If Not IsNull(Me.Email) Then
strWhere = strWhere & " And ShipTo_Email='" & Me.Email & "'"
End If
If Not IsNull(Me.OrderNum) Then
strWhere = strWhere & " And Order_ID=" & Me.OrderNum
End If
DoCmd.OpenForm "INQUIRY_MAIN_View", , , strWhere
DoCmd.Close acForm, "INQUIRY_MAIN"
End Sub

Also, what could I add to this code so that if there are no matching
records, I could have a message pop up that says there are no matching
records? Currently I get an error message Runtime 2427 (no matching value)
Click to expand...


strWhere = strWhere & " AND ShipTo_LName LIKE """ _
& Me.LastName & "*"" "

AFAIK, the error message is caused by something other than
just not finding a record. Perhaps there is some code in
the form that won't work if there are no records?
 
Thank you! I think I will need to research the error handling a little more
before I continue, thanks!
-gary
 
Thank you! The wild card worked, but the error catch did not. No worries
though, I have enough other avenues to try, thank you!
-gary
 
Back
Top