Go Premium for a chance to win a PS4. Enter to Win


Multiple Fields in an Access Query

Posted on 2004-08-16
Medium Priority
Last Modified: 2012-06-22
I have a table where the data in two fields relate to the same thing.

For Example:

Name               Address               Suburb                    State Council 1          State Council 2
Phil                  Over-the-Hill         Far Away                Anderson                   Peterson
Michael             Lost Tree            Neverneverland        Clarke                       Louis
Gregory            Sherwood            Red Riding Hood       Phelps                      Anderson

In the above example, I would like to be able to have a query that allows me to show the records that contain 'Anderson'. Ultimately, I would like to have the option to enter in the search data, but first - the baby steps.

Question by:mhpountney
  • 3
  • 3
  • 2
  • +2

Expert Comment

ID: 11817184
select * from yourTable where  ([Name] = 'Anderson') or ([Address]='Anderson') or ([Suburb]='Anderson') or
([State Council 1]='Anderson') or ([State Council 2]='Anderson')

LVL 34

Expert Comment

ID: 11817199
select * from yourTable where  ([Name] = [Enter Your Serch]) or ([Address]=[Enter Your Serch]) or ([Suburb]=[Enter Your Serch]) or
([State Council 1]=[Enter Your Serch]) or ([State Council 2]=[Enter Your Serch])

Run it and you will get prompted.

LVL 34

Expert Comment

ID: 11817203
my bad

select * from yourTable where ([Name] = [Enter Your Search]) or ([Address]=[Enter Your Search]) or ([Suburb]=[Enter Your Search]) or
([State Council 1]=[Enter Your Search]) or ([State Council 2]=[Enter Your Search]);
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 11817540
I only want to enter in the search request once, when it comes to the search.

ie: [enter your search] for State Council

will mean I can type in 'Anderson', and all 'State Council' records containing Anderson will show up.
LVL 34

Accepted Solution

flavo earned 500 total points
ID: 11817561
the SQL i posted, you will only have to enter it once and it will sreach all fields.

If you only want the state council 's then use

select * from yourTable where ([State Council 1]=[Enter Your Search]) or ([State Council 2]=[Enter Your Search]);

If thats not what you want then explain exactly what you are after..


Expert Comment

ID: 11817588
Than you have to build the SQL Statement from the form's code,
in this question you asked for all the fields at the same time.

something like:

s = "Select * from yourTable where (0=0) " 
if len(textSearch1.value)>0 then
  s = s+"and (field1 = '"+textSearch1.value+"')"
end if

... and so on



Author Comment

ID: 11817609
I'll admit I'm a little slow, but I am not sure where the select*... should be placed in reference to my query.
LVL 39

Expert Comment

ID: 11818108
You would get a SELECT * if you grab the * at the top of you field names and dragged it onto the fields section of the query designer.

SELECT * is in the actual SQL of the query. When you design a query one of the View options is SQL that allows you to see this.

* means: give me all of the fields in the table


Expert Comment

ID: 11824652
Did you ever get this one figured out.  I might be able to help.

Expert Comment

ID: 11826558
From Forms Tab:
  Create a form in design view
  put on the Form from toolbox:
         5 textBox
         1 button
         1 listbox
select these objects one by one and from View\Properties\ Other tab
set their names to
         txtName, txtAddress, txtSuburb, txtState1, txtState2

Select the button, from properties, events tab, on the on Click event
put this code:

Private Sub cmdSearch_Click()
s = "Select * from Table1 where (0=0) "

If Len(txtName.Value) > 0 Then
  s = s + "and ([Name] = '" + txtName.Value + "')"
End If

If Len(txtAddress.Value) > 0 Then
  s = s + "and ([Address] = '" + txtAddress.Value + "')"
End If

If Len(txtSuburb.Value) > 0 Then
  s = s + "and ([Suburb] = '" + txtSuburb.Value + "')"
End If

If Len(txtState1.Value) > 0 Then
  s = s + "and ([State Council 1] = '" + txtState1.Value + "')"
End If

If Len(txtState2.Value) > 0 Then
  s = s + "and ([State Council 2] = '" + txtState2.Value + "')"
End If

lstData.RowSource = s

End Sub

Good luck,

Expert Comment

ID: 11844062
Try this:
Create a form called: frm_Who
Put one control on the form called: Who
Create a select qry called: qry_who
Add all of the fields from your table to the query
On the first criteria line under StateCouncil1 add the line:
On the second line under StateCouncil2 add the line:

These two lines will act as an "Or" statement.

Go to your form and enter the name of a council member that you want to search for.
Be sure to hit enter to commit the name.

Go run your query and see if it works.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question