Parameter Queries

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

Guest

I have a parameter query that filters date and another field "Step" for my
reports.
This works fine except now I need to be able to choose more than one Step
for my report.

tblStep has at least four entries Step 2, Step 3, Step 4, Step 5...each step
is related to specific data.

Can I show results for Step 2 and Step 3?

Thank you...Wendy
 
Sounds like you have a denormalized table structure that is using multiple
fields for the 'same type of value' -- namely Step.

You'll save much frustration later if you revise the structure of your table
to eliminate the Step1, Step2, etc. fields, and instead use a new child
table (call it tblSteps) that has three fields:
FieldToLinkToParentTable
StepNumber
StepValue
Then you can have multiple records where each record is a step. Then your
query would have an expression that uses a step number to get the results
you seek.

If this seems to be an unsatisfactory change to make for whatever reason,
then it's possible, but can be fairly complicated, to write criteria
expressions to get the data you want -- but you'll need to tell us more
about the current query (its SQL statement) and what specific types of
results you want to show in the report.
 
Step 2, Step 3 etc are specific accounting references. As it stands now I can
run a report for each step seperately...what I would like is to run a report
showing data for Step 2 and Step 3...or any combination of Steps. This would
be similar to the categories example microsoft uses...I could choose
beverages or condiments...could I run a report for both condiments and
beverages using a parameter query?

[Forms]![frmMainPrint]![StepType] is in the criteria StepType refers to a
combo box on the frmMainPrint where I can enter Step 2, Step 3 etc.

Could I nclude a range parameter in the Or section where I could select both
values at once?
 
I don't have a good "mental" picture of exactly what you want to do, so
allow me some general comments and we can continue from here.

You can search multiple fields for the same value in a parameter query this
way (where either field has a match):

SELECT * FROM TableName
WHERE Field1 Like "*" & [Enter string:] & "*"
OR Field2 Like "*" & [Enter string:] & "*";


You can search multiple fields for the same value in a parameter query this
way (where both fields have a match):

SELECT * FROM TableName
WHERE Field1 Like "*" & [Enter string:] & "*"
AND Field2 Like "*" & [Enter string:] & "*";


You can search multiple fields for a different value in each field this way
(where either field has a match):

SELECT * FROM TableName
WHERE Field1 Like "*" & [Enter string 1:] & "*"
OR Field2 Like "*" & [Enter string 2:] & "*";


You can search multiple fields for a different value in each field this way
(where both fields have a match):

SELECT * FROM TableName
WHERE Field1 Like "*" & [Enter string 1:] & "*"
AND Field2 Like "*" & [Enter string 2:] & "*";


Using a combo box, you can only search for one value at a time:

SELECT * FROM TableName
WHERE Field1 Like "*" & Forms!FormName!ComboBoxName & "*"
OR Field2 Like "*" & Forms!FormName!ComboBoxName & "*";

SELECT * FROM TableName
WHERE Field1 Like "*" & Forms!FormName!ComboBoxName & "*"
AND Field2 Like "*" & Forms!FormName!ComboBoxName & "*";


Using a list box, you can search for various values at one time, but you
must use a mutltiselect list box and you must use VBA programming to build
the SQL string. I have a sample database that shows how this is done (the
database also shows how to use various controls for entering filtering
values):
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm


Does this help provide a context for additional questions?
--

Ken Snell
<MS ACCESS MVP>



Wendy said:
Step 2, Step 3 etc are specific accounting references. As it stands now I
can
run a report for each step seperately...what I would like is to run a
report
showing data for Step 2 and Step 3...or any combination of Steps. This
would
be similar to the categories example microsoft uses...I could choose
beverages or condiments...could I run a report for both condiments and
beverages using a parameter query?

[Forms]![frmMainPrint]![StepType] is in the criteria StepType refers to a
combo box on the frmMainPrint where I can enter Step 2, Step 3 etc.

Could I nclude a range parameter in the Or section where I could select
both
values at once?

Ken Snell (MVP) said:
Sounds like you have a denormalized table structure that is using
multiple
fields for the 'same type of value' -- namely Step.

You'll save much frustration later if you revise the structure of your
table
to eliminate the Step1, Step2, etc. fields, and instead use a new child
table (call it tblSteps) that has three fields:
FieldToLinkToParentTable
StepNumber
StepValue
Then you can have multiple records where each record is a step. Then your
query would have an expression that uses a step number to get the results
you seek.

If this seems to be an unsatisfactory change to make for whatever reason,
then it's possible, but can be fairly complicated, to write criteria
expressions to get the data you want -- but you'll need to tell us more
about the current query (its SQL statement) and what specific types of
results you want to show in the report.
Click to expand...
Click to expand...
 
Back
Top