By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

need to search for partial instead of exact match

P: n/a
I have the following code attached to a search button on a form that runs a
query. It works great, except that the search for Last Name only returns
exact matches. It is even case sensitive. Anybody have an idea of what I
need to add or change. It is the first If Then statement that I need to
search on partial matches. I've tried to use the like operator, but I just
get syntax errors.

If Not IsNothing(Me.LastName) Then
strSearch = "[LastName]= " & "'" & Me![LastName] & "'"
End If

If Not IsNothing(Me.ssNum) Then
strSearch = "[ssNumber]=" & "'" & Me![ssNum] & "'"
End If

If Not IsNothing(Me.ClientId) Then
strSearch = "[tblClient.ClientId]=" & "'" & Me![ClientId] & "'"
End If

If Not IsNothing(Me.phNum) Then
strSearch = "[Phone1]=" & "'" & Me![phNum] & "'"
End If

If IsNothing(strSearch) Then
MsgBox "No criteria specified.", 32
Exit Sub
End If
DoCmd.OpenForm "frmClaimMain", , , strSearch

If Forms!frmClaimMain.RecordsetClone.RecordCount = 0 Then
MsgBox "No Clients meet your criteria", 64
DoCmd.ShowAllRecords
Me.Visible = True
Exit Sub
End If
DoCmd.Close acForm, Me.Name
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
John, thank you.
You can't imagine what you just did for me. Those 2 stars you put in my code
were the reason for my syntax error when I tried to use the like operator.
This application was nearly finished 6 months ago and that little problem
caused me to put it away for awhile. I was really under pressure to get it
finished. Now I can tie up the loose ends. Thanks again.

You asked to see my IsNothing function. Here it is.

Function IsNothing(varToTest As Variant) As Integer
' Tests for a "logical" nothing based on data type
' Empty and Null = Nothing
' Number = 0 is Nothing
' Zero length string is Nothing
' Date/Time is never Nothing

IsNothing = True

Select Case VarType(varToTest)
Case vbEmpty
Exit Function
Case vbNull
Exit Function
Case vbBoolean
If varToTest Then IsNothing = False
Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
If varToTest <> 0 Then IsNothing = False
Case vbDate
IsNothing = False
Case vbString
If (Len(varToTest) <> 0 And varToTest <> " ") Then IsNothing =
False
End Select

End Function
"John Mishefske" <mi****@execpc.com> wrote in message
news:vm************@corp.supernews.com...
Larry wrote:
I have the following code attached to a search button on a form that runs a query. It works great, except that the search for Last Name only returns
exact matches. It is even case sensitive. Anybody have an idea of what I
need to add or change. It is the first If Then statement that I need to
search on partial matches. I've tried to use the like operator, but I just get syntax errors.
Try

strSearch = "[LastName] Like '*' & '" & Me![LastName] & "' & '*'"
If Not IsNothing(Me.LastName) Then
strSearch = "[LastName]= " & "'" & Me![LastName] & "'"
End If


We don't have the code to your IsNothing() procedure - can we take a look?
If Not IsNothing(Me.ssNum) Then
strSearch = "[ssNumber]=" & "'" & Me![ssNum] & "'"
End If


This is going to overwrite the previous strSearch - were you hoping to

match multiple criteria? In other words, only the last control with some value in it is going to be passed to the "frmClaimMain" form.
If Not IsNothing(Me.ClientId) Then
strSearch = "[tblClient.ClientId]=" & "'" & Me![ClientId] & "'"
End If

If Not IsNothing(Me.phNum) Then
strSearch = "[Phone1]=" & "'" & Me![phNum] & "'"
End If

If IsNothing(strSearch) Then
MsgBox "No criteria specified.", 32
Exit Sub
End If


DoCmd.OpenForm "frmClaimMain", , , strSearch

If Forms!frmClaimMain.RecordsetClone.RecordCount = 0 Then
MsgBox "No Clients meet your criteria", 64
DoCmd.ShowAllRecords
Me.Visible = True
Exit Sub
End If
DoCmd.Close acForm, Me.Name


--
'-------------------------------
' John Mishefske
'-------------------------------

Nov 12 '05 #2

P: n/a
Larry wrote:
John, thank you.
You can't imagine what you just did for me. Those 2 stars you put in my code
were the reason for my syntax error when I tried to use the like operator.
This application was nearly finished 6 months ago and that little problem
caused me to put it away for awhile. I was really under pressure to get it
finished. Now I can tie up the loose ends. Thanks again.
You're welcome. Glad I could help.
You asked to see my IsNothing function. Here it is.


I just thought it may be part of the problem.

--
'-------------------------------
' John Mishefske
'-------------------------------

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.