Returning all instances

S

Sarah Stockton

I have a form that is called "Customer Search", this box filters the records
in my database for any matches to the first name, last name, or social sec
num., in any combination. If a match is found it closes the search box and
opens a payment form. If no match is found it closes the search box and
returns message stating that the "customer does not exist". This works great
until I have more than one instance of the information entered, it closes the
search box and returns the message "customer does not exist". I have
provided my code below.

Public Function getCustomerData() As String

Dim whereString As String

DoCmd.OpenForm "Frm_SearchCustomer", , , , , acDialog

If custLastName = "" And custFirstName = "" And custSS = "" Then
MsgBox "No info entered."
Else ' something specified

If custLastName <> "" Then 'last name provided
whereString = "LastName='" & custLastName & "'"
End If

If custFirstName <> "" Then 'first name provided
whereString = whereString & " AND FirstName='" & custFirstName &
"'"
End If

If custSS <> "" Then 'ss provided
whereString = whereString & " AND SocialSecNumber='" & custSS &
"'"
End If

If Left(whereString, 5) = " AND " Then
'drop leading 'and'
whereString = Right(whereString, Len(whereString) - 5)
End If

If Len(whereString) > 0 Then
'whereString = " WHERE (" & whereString & ");"
End If

getCustomerData = whereString

Any sugestions would be greatly appreciated.
Thank you
 
Top