Need help with Search Engine for my form

[Dbar10]

I am working on a form in my application for a Worker Profile evrything is working fine except the Search Box. I have to be able to search on two criteria. One is the WorkStatus i.e. Active, Inactive,etc. and 1 of three other choices. First Name, Last Name or SSN. I have pIaced 4 unbound text boxes on my form tied to the select query that pulls all the records called Work Add/Edit Query. I can run with the status and it works fine but how do I include one of my other criteria with that? As soon as I add another criteria to my query it doesn't work. I am stumped. Doug

[ADezii]

Assuming your specifications and the Table's Field Names as you describe them, and the Field Names on the Form are txtLastName, txtFirstName, and txtSSN, the following code should suit your needs:

Dim strCriteria As String

strCriteria = "Select * From tblEmployee Where [tblEmployee].[Status] = '"

'Need Status Field poppulated as well as 1 other Criteria Field
If IsNull(Me![txtStatus]) Then
  Exit Sub
Else
 strCriteria = strCriteria & Me![txtStatus] & "' And "
End If

'Need at least 1 of the 3 Fields with a Value contained within it, if not
'get outta Dodge!
If IsNull(Me![txtFirstName]) And IsNull(Me![txtLastName]) And IsNull(Me![txtSSN]) Then
  Exit Sub
Else    'At least 1 of these Fields contain a Value
  If Not IsNull(Me![txtFirstName]) Then    'Got a First Name!
    strCriteria = strCriteria & "[tblEmployee].[First Name] = '" & Me![txtFirstName] & "';"
      Me.RecordSource = strCriteria
        Exit Sub      'Met the 1 Criteria Rule
  ElseIf Not IsNull(Me![txtLastName]) Then    'Got a Last Name but no First Name!
    strCriteria = strCriteria & "[tblEmployee].[Last Name] = '" & Me![txtLastName] & "';"
      Me.RecordSource = strCriteria
        Exit Sub      'Met the 1 Criteria Rule
  Else      'No First or Last Name, but a SSN
    strCriteria = strCriteria & "[tblEmployee].[SSN] = '" & Me![txtSSN] & "';"
    Me.RecordSource = strCriteria
      Exit Sub      'Met the 1 Criteria Rule
  End If
End If

'Test the SQL String
Me.RecordSource = strCriteria

[Dbar10]

Thank you vrey much. this worked

[ADezii]

Thank you vrey much. this worked

Glad it worked for you.