Looping through Parameter Query

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

Guest

I currently have a parameter query that runs from an unbound form where I can
select from 95 different criteria and then run the query. This is great, but
I would really like to be able to loop through the 95 criteria without having
to select each one individually. Is this possible?
 
Hi,


Using a table, one column, 95 records, and an inner join? Sure, it depends
of you exact query, but this is the avenue that I would explore.


Hoping it may help,
Vanderghast, Access MVP
 
I currently have a parameter query that runs from an unbound form where I can
select from 95 different criteria and then run the query. This is great, but
I would really like to be able to loop through the 95 criteria without having
to select each one individually. Is this possible?
Click to expand...

You have 95 FIELDS in your table with criteria on each!? Yow.

You can use a naming convention for the controls on the form - e.g.
name the textboxes crit1, crit2, crit3, ... crit95; have an array of
the fieldnames that correspond to each; and loop through with code
like

Dim i As Integer
Dim strCtl As String
For i = 1 to 95
strCtl = "crit" & i
If Not IsNull(Me.Controls(strCtl)) Then
strSQL = strSQL & " AND [" & strFieldnames(i) & "]=" _
& Me.Controls(strCtl)
End If
Next i


John W. Vinson[MVP]
 
Thank you both for your ideas.

John Vinson said:
I currently have a parameter query that runs from an unbound form where I can
select from 95 different criteria and then run the query. This is great, but
I would really like to be able to loop through the 95 criteria without having
to select each one individually. Is this possible?
Click to expand...

You have 95 FIELDS in your table with criteria on each!? Yow.

You can use a naming convention for the controls on the form - e.g.
name the textboxes crit1, crit2, crit3, ... crit95; have an array of
the fieldnames that correspond to each; and loop through with code
like

Dim i As Integer
Dim strCtl As String
For i = 1 to 95
strCtl = "crit" & i
If Not IsNull(Me.Controls(strCtl)) Then
strSQL = strSQL & " AND [" & strFieldnames(i) & "]=" _
& Me.Controls(strCtl)
End If
Next i


John W. Vinson[MVP]
Click to expand...
 
Hi,


Note that I saw your question as having one column for which you want try up
to 95 different value for a parameter (like State="CA", or State="NY",
.... and so on, up to 95 values). If, INSTEAD, you have 95 FIELDS, each of
them having to get one value, applying an INNER JOIN as I described is not
necessary appropriate.


Hoping it ma help,
Vanderghast, Access MVP


CCross said:
Thank you both for your ideas.

John Vinson said:
I currently have a parameter query that runs from an unbound form where
I can
select from 95 different criteria and then run the query. This is
great, but
I would really like to be able to loop through the 95 criteria without
having
to select each one individually. Is this possible?
Click to expand...

You have 95 FIELDS in your table with criteria on each!? Yow.

You can use a naming convention for the controls on the form - e.g.
name the textboxes crit1, crit2, crit3, ... crit95; have an array of
the fieldnames that correspond to each; and loop through with code
like

Dim i As Integer
Dim strCtl As String
For i = 1 to 95
strCtl = "crit" & i
If Not IsNull(Me.Controls(strCtl)) Then
strSQL = strSQL & " AND [" & strFieldnames(i) & "]=" _
& Me.Controls(strCtl)
End If
Next i


John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Back
Top