Solved

Multiple Fields in an Access Query

Posted on 2004-08-16
13
257 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

789 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