How to use DLOOKUP for partial search? 1

[Mayo123]

I tried to find records by using last 4 digits of ssn in dlookup() function, but somehow it didn't work. Can I use the function for partial searching? Thanks for your advice!

strLName = DLookup("lname", "MyTable", "[SSN] ='" & "*" & Forms![FindForm]![txtPartialSSN] & "'")
If IsNull(strLName) Then
MsgBox "This SSN doesn't exist in MyTable!", vbOKOnly, "Info"
Me.txtPartialSSN.SetFocus
Me.txtPartialSSN = ""
Exit Sub
Else
stDocName = "MyForm"
........

 

[PHV]

strLName = DLookup("lname", "MyTable", "SSN LIKE '*" & Forms![FindForm]![txtPartialSSN] & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mayo123]

You're always right, PHV. I should have used the keyword like.
Thank you so much.

 

[PHV]

I should have used the keyword like
Another way:
strLName = DLookup("lname", "MyTable", "Right([SSN],4)='" & Forms![FindForm]![txtPartialSSN] & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mayo123]

Perfect! Both worked! Thank you so much!
But I might have to give up using dlookup(), because it only shows one record at a time.