mhpountney
asked on
Multiple Fields in an Access Query
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
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
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
([State Council 1]=[Enter Your Serch]) or ([State Council 2]=[Enter Your Serch])
Run it and you will get prompted.
Dave
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]);
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]);
ASKER
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.
ie: [enter your search] for State Council
will mean I can type in 'Anderson', and all 'State Council' records containing Anderson will show up.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
I'll admit I'm a little slow, but I am not sure where the select*... should be placed in reference to my query.
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
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
Did you ever get this one figured out. I might be able to help.
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
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
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_Wh o]![who]), [tbl_1]![S tatecounci l1],[forms ]![frm_Who ]![Who])
On the second line under StateCouncil2 add the line:
IIf(IsNull([forms]![frm_Wh o]![who]), [tbl_1]![S tatecounci l1],[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.
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_Wh
On the second line under StateCouncil2 add the line:
IIf(IsNull([forms]![frm_Wh
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.
([State Council 1]='Anderson') or ([State Council 2]='Anderson')
Peter