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

How do you Search Multiple fields.....??

P: 8
Good Day Good People...

I have a table with various fields, a number of fields relate to the same data type, i.e. Language1, Language2, Language3, Language4, - I want to be able to search all of the fields for e.g. Spanish.
I currently use a combo list which is unbound, but without the user having to manually select each Language field to search in, i want the search to look in all of the Language fields automatically by only selecting, say "Language".

Below is the code used to search the fields....
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.  
  3.     If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
  4.         MsgBox "You must select a field to search."
  5.  
  6.     ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
  7.         MsgBox "You must enter a search string."
  8.  
  9.     Else
  10.  
  11.         'Generate search criteria
  12.         GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
  13.  
  14.         'Filter frm_Report based on search criteria
  15.         Form_frm_ReportCam.RecordSource = "select * from providers_cam where " & GCriteria
  16.         Form_frm_ReportCam.Caption = "providers_cam (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
  17.  
  18.         DoCmd.OpenForm "frm_ReportCam"
  19.  
  20.        'Close frm_SearchBoxCam
  21.         DoCmd.Close acForm, "frm_SearchBoxCam"
  22.  
  23.     End If
  24.  
  25. End Sub
  26.  
If you need more info will be glad to provide... if anyone has any ideas i will be, well, just glad.

cheers.
Aug 10 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,669
Good Day Good People...

I have a table with various fields, a number of fields relate to the same data type, i.e. Language1, Language2, Language3, Language4, - I want to be able to search all of the fields for e.g. Spanish.
I currently use a combo list which is unbound, but without the user having to manually select each Language field to search in, i want the search to look in all of the Language fields automatically by only selecting, say "Language".

Below is the code used to search the fields....

ate Sub cmdSearch_Click()

If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

'Filter frm_Report based on search criteria
Form_frm_ReportCam.RecordSource = "select * from providers_cam where " & GCriteria
Form_frm_ReportCam.Caption = "providers_cam (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

DoCmd.OpenForm "frm_ReportCam"

'Close frm_SearchBoxCam
DoCmd.Close acForm, "frm_SearchBoxCam"

End If

End Sub

If you need more info will be glad to provide... if anyone has any ideas i will be, well, just glad.

cheers.
The following basic Code Template should provide the solution for you. It has been tested and is fully functional:
Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2.  
  3. MySQL = "SELECT *FROM Providers_Cam WHERE Language1 Like " & "'*" & Me![txtSearchString] & "*'"
  4. MySQL = MySQL & " OR Language2 Like " & "'*" & Me![txtSearchString] & "*'"
  5. MySQL = MySQL & " OR Language3 Like " & "'*" & Me![txtSearchString] & "*'"
  6. MySQL = MySQL & " OR Language4 Like " & "'*" & Me![txtSearchString] & "*'"
  7.   Me.RecordSource = MySQL
Aug 11 '07 #2

P: 8
The following basic Code Template should provide the solution for you. It has been tested and is fully functional:
Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2.  
  3. MySQL = "SELECT *FROM Providers_Cam WHERE Language1 Like " & "'*" & Me![txtSearchString] & "*'"
  4. MySQL = MySQL & " OR Language2 Like " & "'*" & Me![txtSearchString] & "*'"
  5. MySQL = MySQL & " OR Language3 Like " & "'*" & Me![txtSearchString] & "*'"
  6. MySQL = MySQL & " OR Language4 Like " & "'*" & Me![txtSearchString] & "*'"
  7.   Me.RecordSource = MySQL

This is extremely useful... however just having trouble intergrating it into the original code... could you point me in the right direction.

Thanks

"The biggest problem encountered while trying to design a system that was completely foolproof, was, that people tended to underestimate the ingenuity of complete fools."
Aug 29 '07 #3

ADezii
Expert 5K+
P: 8,669
This is extremely useful... however just having trouble intergrating it into the original code... could you point me in the right direction.

Thanks

"The biggest problem encountered while trying to design a system that was completely foolproof, was, that people tended to underestimate the ingenuity of complete fools."
I'm sorry, but I seem a litlle confused and/or rusty trying to figure out exactly
what you are searching for.
  1. What are some values contained in cboSearchField and how do they relate to txtSearchString?
  2. What are some possible txtSearchString Field values?
  3. Write a typical search request indicating a value selected from cboSearchField, and an entry in txtSearchString.
Aug 30 '07 #4

Post your reply

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