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

Need help with Search Engine for my form

P: 17
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
Nov 24 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strCriteria As String
  2.  
  3. strCriteria = "Select * From tblEmployee Where [tblEmployee].[Status] = '"
  4.  
  5. 'Need Status Field poppulated as well as 1 other Criteria Field
  6. If IsNull(Me![txtStatus]) Then
  7.   Exit Sub
  8. Else
  9.  strCriteria = strCriteria & Me![txtStatus] & "' And "
  10. End If
  11.  
  12. 'Need at least 1 of the 3 Fields with a Value contained within it, if not
  13. 'get outta Dodge!
  14. If IsNull(Me![txtFirstName]) And IsNull(Me![txtLastName]) And IsNull(Me![txtSSN]) Then
  15.   Exit Sub
  16. Else    'At least 1 of these Fields contain a Value
  17.   If Not IsNull(Me![txtFirstName]) Then    'Got a First Name!
  18.     strCriteria = strCriteria & "[tblEmployee].[First Name] = '" & Me![txtFirstName] & "';"
  19.       Me.RecordSource = strCriteria
  20.         Exit Sub      'Met the 1 Criteria Rule
  21.   ElseIf Not IsNull(Me![txtLastName]) Then    'Got a Last Name but no First Name!
  22.     strCriteria = strCriteria & "[tblEmployee].[Last Name] = '" & Me![txtLastName] & "';"
  23.       Me.RecordSource = strCriteria
  24.         Exit Sub      'Met the 1 Criteria Rule
  25.   Else      'No First or Last Name, but a SSN
  26.     strCriteria = strCriteria & "[tblEmployee].[SSN] = '" & Me![txtSSN] & "';"
  27.     Me.RecordSource = strCriteria
  28.       Exit Sub      'Met the 1 Criteria Rule
  29.   End If
  30. End If
  31.  
  32. 'Test the SQL String
  33. Me.RecordSource = strCriteria
  34.  
Nov 24 '08 #2

P: 17
Thank you vrey much. this worked
Dec 5 '08 #3

ADezii
Expert 5K+
P: 8,597
@Dbar10
Glad it worked for you.
Dec 5 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Browse more Microsoft Access / VBA Questions on Bytes