Multiple Fields in an Access Query

Posted on 2004-08-16
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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]);
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.


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 125 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 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