Solved

Multiple Fields in an Access Query

Posted on 2004-08-16
13
256 Views
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.

Thanks
0
Comment
Question by:mhpountney
  • 3
  • 3
  • 2
  • +2
13 Comments
 
LVL 5

Expert Comment

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

Peter
0
 
LVL 34

Expert Comment

by:flavo
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.

Dave
0
 
LVL 34

Expert Comment

by:flavo
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]);
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:mhpountney
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.
0
 
LVL 34

Accepted Solution

by:
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..

Dave
0
 
LVL 5

Expert Comment

by:peterpuscas
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


Peter



0
 

Author Comment

by:mhpountney
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.
0
 
LVL 39

Expert Comment

by:stevbe
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

Steve
0
 

Expert Comment

by:pmcelhany
ID: 11824652
Did you ever get this one figured out.  I might be able to help.
0
 
LVL 5

Expert Comment

by:peterpuscas
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
         cmdSearch
         lstData

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
lstData.Requery

End Sub


Good luck,
Peter
0
 

Expert Comment

by:pmcelhany
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:
IIf(IsNull([forms]![frm_Who]![who]),[tbl_1]![Statecouncil1],[forms]![frm_Who]![Who])
On the second line under StateCouncil2 add the line:
IIf(IsNull([forms]![frm_Who]![who]),[tbl_1]![Statecouncil1],[forms]![frm_Who]![Who])

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.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

776 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