473,320 Members | 1,883 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
3 2722
ADezii
8,834 Expert 8TB
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
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
8,834 Expert 8TB
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

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

Similar topics

1
by: BillK | last post by:
Hi - I am looking for a solution to a clients requirement. They want to distribute a database of info on CD, which user can search on selected fields and results will be displayed. The data and...
4
by: Gobi | last post by:
Hello, I have a Database with lists of Clients in each. Every year a new tables is created with the naming convention "CloseYear" ie close1999, close2000 There are tables from this year back to...
5
by: JP SIngh | last post by:
Hi All This is a complicated one, not for the faint hearted :) :) :) Please help if you can how to achieve this search. We have a freetext search entry box to allow users to search the...
2
by: Homey! | last post by:
Hello all I am new to Access. I have imported data from an old FoxPro 2.x database. This is probably the most basic function but I cant get a search box to work. I need to search for company name...
5
by: mforema | last post by:
Hi Everyone, I want to search records by typing in multiple keywords. I currently have a search form. It has a combo box, text box, Search command button, and a subform. The combo box lists the...
1
Merlin1857
by: Merlin1857 | last post by:
How to search multiple fields using ASP A major issue for me when I first started writing in VB Script was constructing the ability to search a table using multiple field input from a form and...
1
by: Kye | last post by:
It has been a while since using vb an now I have started a project and I am not sure where my mistake is in my code. Basically what I am doing is trying to build a multiple search string with...
3
by: Redbeard | last post by:
Hi All this is my first time post, be gentle. I am looking at creating a keyword search that searches multiple fields in a Form and then filters records that match the keyword. The Form...
41
by: nik707 | last post by:
Hello all, First of all my name is Shan and I am currently learning and also designing a database in Access. Your forum users seems to be very helpful and experts in this matter so I thought I...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.