Single Query with Differing Search Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a drop-down menu that has 6 choices (A, B, C, D, E, F). They convert
loosely to fields in a table (i.e. Last Name as choice, LName as field). I'm
trying to right a single query that will look at the choice and then pull the
corresponding records.

Have any of you done anything like this? I would greatly appreciate any
help on this.

Thanks.
 
Ed,

You might need to give an example to make your meaning clear. Is the
choice in the combobox supposed to be a criteria in the query?
 
The users have 6 choices: First Name [conFName], Last Name[conLName],
City[conCity], State[conState], Church[conChurch], and Sort
Code[conSortCode]. If the user chooses First Name, Last Name, City, or
State, a field [txtData] becomes visible and active for them to enter the
data into freeform. If Church or Sort Code is chosen, a different field
[txtChurch] or [txtSortCode] becomes visible and active. These two fields
are dropdown menus as well.

What I'm trying to do is have the query pull the fields:
[conID]
[conFName]
[conLame]
[conCity]
[conState]

The problem that I have is that the database is becoming increasingly large.
I've already got almost 60 different queries going on almost as many
forms/subforms, etc. I've got 4 different search forms and I'm trying to
figure out ways to reduce the number of queries, etc.

Thanks for any help.
 
Ed,

Do I understand you correctly, that the user can select an item from a
combobox (I assume this is what you mean by a "drop-down menu"?), being
one of the "6 choices", and then enter something into the txtData
control (I assume it is not actually a field?), and then the data that
they enter into txtData is supposed to be a criteria for the field
referred to by the choice selected in the combobox, am I right? And
then, this is supposed to be reflected in the Rcord Source of a form, is
that right? So for example, they will select 'First Name' from the
combobox, enter "Fred" in the txtData textbox, click a button or some
such, and a form will open showing all records from "some table" where
the conFName is Fred, am I right?
 
Steve,

Yep, that's exactly what I want it to do. I've actually got 4 different
areas that each do this, and I don't really want 24-30 different queries if I
can at all help it.

Thanks for deciphering my problem.
 
Ed,

Ok, one approach would be to use VBA procedure to build the Where
Condition clause for the OpenForm. So, for example...

Dim strCrit As String
Select Case Me.ChoicesCombobox
Case "First Name"
strCrit = "[conFName] = '" & Me.txtData & "'"
Case "Last Name"
strCrit = "[conLName] = '" & Me.txtData & "'"
Case "City"
strCrit = "[conCity] = '" & Me.txtData & "'"
Case "State"
strCrit = "[conState] = '" & Me.txtData & "'"
Case "Church"
strCrit = "[conChurch] = '" & Me.txtChurch & "'"
Case "Sort Code"
strCrit = "[conSortCode] = '" & Me.txtSortCode & "'"
End Select
DoCmd.OpenForm "YourForm", , , strCrit

You might find these interesting/useful:
http://www.fontstuff.com/access/acctut17.htm
http://www.fontstuff.com/access/acctut18.htm
 
A non-code way (although I prefer Steve's solution) is to create a combo box
with properties like:

Name: cboField
Row Source Type: Value list
Column Count: 2
Row Source: 1,First Name,2, Last Name, 3, City, 4, State
Column Widths: 0,1
Bound Column: 1

Then in your query, create a column/field like:
SearchField: Choose(Forms!frmYourForm!cboField, [conFName], [conLName],
[conCity], [conState])
Criteria: Forms!frmYourForm!txtSearchFor


--
Duane Hookom
MS Access MVP


Steve Schapel said:
Ed,

Ok, one approach would be to use VBA procedure to build the Where
Condition clause for the OpenForm. So, for example...

Dim strCrit As String
Select Case Me.ChoicesCombobox
Case "First Name"
strCrit = "[conFName] = '" & Me.txtData & "'"
Case "Last Name"
strCrit = "[conLName] = '" & Me.txtData & "'"
Case "City"
strCrit = "[conCity] = '" & Me.txtData & "'"
Case "State"
strCrit = "[conState] = '" & Me.txtData & "'"
Case "Church"
strCrit = "[conChurch] = '" & Me.txtChurch & "'"
Case "Sort Code"
strCrit = "[conSortCode] = '" & Me.txtSortCode & "'"
End Select
DoCmd.OpenForm "YourForm", , , strCrit

You might find these interesting/useful:
http://www.fontstuff.com/access/acctut17.htm
http://www.fontstuff.com/access/acctut18.htm

--
Steve Schapel, Microsoft Access MVP

Ed said:
Steve, Yep, that's exactly what I want it to do. I've actually got 4
different areas that each do this, and I don't really want 24-30
different queries if I can at all help it. Thanks for deciphering my
problem.
Click to expand...
Click to expand...
 
Back
Top