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

access query to search multiple criterias in multiple fields

P: 3
Good afternoon everyone. I'm trying to work on an Access query so that whenever management enters certain data, it brings it up. I'm close, but no cigar.

Here's the code I have in SQL:

SELECT *
FROM CSR AS Query
WHERE (csr Like "*" & [CSR ID] & "*" ) or (Ongoing_CW Like "*" & [Ongoing CW] & "*") or (Record_No Like "*" & [Record No] & "*") or (Last_Name Like "*" & [Last Name] & "*") or (First_Name Like "*" & [First Name] & "*") or (task1 Like "*" & [Task 1] & "*") or (task2 Like "*" & [Task 2] & "*") or (task3 Like "*" & [Task 3] & "*") or (task4 Like "*" & [Task 4] & "*") or (task5 Like "*" & [Task 5] & "*") or (Date Like "*" & [Date] & "*")

With this, it brings up absolutely everything. If I change the OR to an AND, it brings up nothing.

What i'm looking to do is have a query, so that it brings up the text boxes to query data. Sometimes they might be looking for 2 or more different types of data. Instead of creating multiple queries, I was hoping to have this all in one big query. Any suggestions?
Aug 25 '08 #1
Share this Question
Share on Google+
5 Replies


puppydogbuddy
Expert 100+
P: 1,923
My suggestion is that you build a filter string for the where clause and use a button to turn on the form's filter property via a button. Note that nulls are bypassed on the assumption that if nothing was entered for a particular parameter, that criteria will not be part of the filter. The record source for your form would be as you indicated in your query>>>> Select * from csr. Your filter code would look like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnFilter_Click()
  2.  
  3. On Error GoTo Err_Error_Routine
  4.  
  5. strFilter  =  ""             'initialize filter
  6.  
  7. If  Not IsNull(csr) then strFilter =  "csr Like "*" & [CSR ID] & "*" & "And "
  8. If Not IsNull(Ongoing_CW) then strFilter = "Ongoing_CW Like "*" & [Ongoing CW] & "*" & " And "
  9. If Not IsNull(Record_No) then strFilter = "Record_No Like "*" & [Record No] & "*" & " And "
  10. If Not IsNull(Last_Name) then strFilter = "Last_Name Like "*" & [Last Name] & "*" & " And "
  11. If Not IsNull(First_Name) then strFilter = "First_Name Like "*" & [First Name] & "*" & " And "
  12. If Not IsNull(task1) then strFilter = "task1 Like "*" & [Task 1] & "*" & " And "
  13. If Not IsNull(task2) then strFilter = "task2 Like "*" & [Task 2] & "*" & " And "
  14. If Not IsNull(task3) then strFilter = "task3 Like "*" & [Task 3] & "*" & " And "
  15. If Not IsNull(task4) then strFilter = "task4 Like "*" & [Task 4] & "*" & " And "
  16. If Not IsNull(task5) then strFilter = "task5 Like "*" & [Task 5] & "*" & " And "
  17. If Not IsNull(Date) then strFilter = "Date Like "*" & [Date] & "*" & " And "
  18.  
  19. If strFilter <> "" Then
  20.     strFilter = Left$(strFilter, Len(strFilter) - 5)          'eliminate trailing " And "
  21.     Me.Filter = strFilter       ' set Form filter to strFilter
  22.     Me.FilterOn = True
  23.  
  24. Exit_Continue:
  25.         Exit Sub
  26. Error_Routine:
  27.         MsgBox "Error# " & Err.Number & " " & Err.Description
  28.         Resume Exit_Continue
  29. End Sub
Aug 25 '08 #2

P: 3
I created a form based off of the query and added the button, is that correct?
Aug 25 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
I created a form based off of the query and added the button, is that correct?
Yes. I thought you had a form already, but if you don't then you need to create one with its record source set to >>>>>Select * From CSR

then add comboboxes, textboxes, etc for the user to enter each of the selection criteria
----------------------------------------------------------

keep in mind that you may have to debug each criteria by adding them to the filter string one at a time.....by that, I mean on the first go around with the debugger, you would comment out all of your filter string components, except the first one. After you get it working with the first one, then uncomment out the second string component.....repeat the process until each/all components
of the filter string is debugged.

I see two probable reasons why you will have debug errors......
1. I did not know whether the string return value was numeric or text ....and thererfore, used your syntax.....which may be incorrect.
2. your date criteria may cause you problems.....Date is a reserved word in Access so you should probably change the field name in your table from Date to csrDate or something like that. Also, the date entered on the form by the user will probably need delimiters in the string to identify it as a date >>>
e.g .............. # & csrDate & #
Aug 25 '08 #4

P: 3
I totally forgot to let you know what type of field each is.

Date = date/time
CSR = number
Ongoing CW = number
Record No = Number
last name = text
first name = text
category = text
task 1 through task 5 = text
notes = memo
Aug 26 '08 #5

puppydogbuddy
Expert 100+
P: 1,923
try the folowing with the syntax modified for the data type:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnFilter_Click()
  2.  
  3. On Error GoTo Err_Error_Routine
  4.  
  5. Dim strFilter As string
  6.  
  7. strFilter  =  ""             'initialize filter
  8.  
  9. If  Not IsNull(Me![CSR ID]) then strFilter = strFilter &  "csr = " & "*" & Me![CSR ID]  & "*" & " And "
  10. If Not IsNull(Me![Ongoing CW]) then strFilter = strFilter & "Ongoing_CW = " & "*" & Me![Ongoing CW] & "*" & " And "
  11. If Not IsNull(Me![Record No]) then strFilter = strFilter & "Record_No = " & "*" & Me![Record No] & "*" & " And "
  12. If Not IsNull(Last_Name) then strFilter = strFilter & "Last_Name Like " & Chr(34) & "*"  [Last Name] & "*" & Chr(34) & " And "
  13. If Not IsNull(First_Name) then strFilter = strFilter & "First_Name Like " & Chr(34) & "*" & [First Name] & "*" & Chr(34) & " And "
  14. If Not IsNull(Me![Task 1]) then strFilter = strFilter & "task1 Like " & Chr(34) & "*" & Me![Task 1] & "*" & Chr(34) & " And "
  15. If Not IsNull(Me![Task 2]) then strFilter = strFilter & "task2 Like " & Chr(34) & "*" & Me![Task 2] & "*" & Ch(34) & " And "
  16. If Not IsNull(Me![Task 3]) then strFilter = strFilter & "task3 Like " & Chr(34) & "*" & Me![Task 3] & "*" & Chr(34) & " And "
  17. If Not IsNull(Me![Task 4]) then strFilter = strFilter & "task4 Like " & Chr(34) & "*" & Me![Task 4] & "*" & Chr(34) & " And "
  18. If Not IsNull(Me![Task 5]) then strFilter = strFilter & "task5 Like " & Chr(34) & "*" & Me![Task 5] & "*" &Chr (34) & " And "
  19. If Not IsNull(Me![Date]) then strFilter = strFilter & "Date Like "*" & "#" & Me![Date] & "#" & "*" & " And "
  20.  
  21. If strFilter <> "" Then
  22.     strFilter = Left$(strFilter, Len(strFilter) - 5)          'eliminate trailing " And "
  23.     Forms!YourForm.Filter = strFilter       ' pass filter to Form
  24.     Me.FilterOn = True
  25.  
  26. 'alternatively you could use: OpenForm "YourForm",,, strFilter
  27.  
  28. Exit_Continue:
  29.         Exit Sub
  30. Error_Routine:
  31.         MsgBox "Error# " & Err.Number & " " & Err.Description
  32.         Resume Exit_Continue
  33. End Sub
Aug 27 '08 #6

Post your reply

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