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
'-------------------------------
