Passing parameters on to subquries

[Conan Kelly]

Hello all,

I'm going to try to create a UNION query that will sum balances from 16 other queries. In each of these queries is data for 2
states. I would like to have the query to ask the user for which state they want data only once at the begining of the query and
then pass that parameter on to each of the other queries in each part of the UNION query.

Is this possible? Please post some sample SQL text on how to do this. I have no idea on where to begin. Can I have variables in
SQL text that I can assign a value to?

 

[Allen Browne]

Try declaring your PARAMETERS on the first line of the query, i.e. before
the first SELECT.

With a normal query (not UNION), you can get Access to generate this line
for you by choosing Parameters on the Query menu. Then switch to SQL View
(View menu), and copy the example you see there.

An alternative would be to use a form where the user enters the values, and
then refer to that same location in each query, e.g.:
[Forms].[Form1].[Text0]

 

[Jamie Collins]

Try declaring your PARAMETERS on the first line of the query, i.e. before
the first SELECT.

With a normal query (not UNION), you can get Access to generate this line
for you by choosing Parameters on the Query menu.

The CREATE PROCEDURE syntax has an advantage over the PARAMETERS syntax
(in addition to not choking on a UNION query <g>) because one can
specify a default for the parameter values e.g. here param_stock_date
is given a default value equal to the current date:

CREATE PROCEDURE MyProc
(param_stock_date DATETIME = DATE())
AS
SELECT stock_date, unit_price, 'LIFO' As stock_type
FROM LIFO
WHERE stock_date = param_stock_date
UNION ALL
SELECT stock_date, unit_price, 'FIFO' As stock_type
FROM FIFO
WHERE stock_date = param_stock_date

Jamie.

--

 

[Conan Kelly]

Allen,

Thanks for your help.

I used your suggestion about using a form. It works fine. My form is based on a table. My criteria is based on States. We have 6
states that we are summing data on: AL, AZ, CO, FL, NM, TX. So in my States table (Fields: State, StateAbrev, StateCode (all text
fields)) I have 6 records, 1 for each state. So with the form open, I can navigate to the state I want to get data for and then run
my query.

The problem I'm having is that at times I want to get data on all states. I was trying to enter another record in the States table
to accomplish this: State = All; StateAbrev = ALL; StateCode = *. I could not get this to work.

I simplified the query into a single select query (eliminated all the UNIONS) and changed the criteria to a parameter so it will ask
me to enter the state code. I've tried *, "*", Like *, Like "*", 0*, "0*", Like 0*, Like "0*". Nothing seems to work--all of these
combos returned a recordset with 0 records (it will return a recordset with 1 record when I enter an actuall state code--it should
return a recordset with 1 record in it but with balances for all states sumed). (the 0* combinations above are because the
StateCode field for each state is 4 numeric characters (text field, remember) and the first 2 characters are 0's (zeros))

Is there some kind of wild card combination that I can enter into my Table/Form record (or parameter) that will allow me to return
all states?

Thanks again for all of your help,

Conan Kelly

Try declaring your PARAMETERS on the first line of the query, i.e. before the first SELECT.

With a normal query (not UNION), you can get Access to generate this line for you by choosing Parameters on the Query menu. Then
switch to SQL View (View menu), and copy the example you see there.

An alternative would be to use a form where the user enters the values, and then refer to that same location in each query, e.g.:
[Forms].[Form1].[Text0]

 

[Allen Browne]

Try setting the WHERE clause of each of the SELECTs in your UNION query like
this:
WHERE (([Forms].[Form1].[Text0] Is Null)
OR ([State] = [Forms].[Form1].[Text0]))

If the WHERE evalautes to True for a record, that one is included; otherwise
it is rejected. There are 2 ways the WHERE clause above can be true:
a) if the text box is null, the first part is true,
b) if the text box has the same value as the field, the 2nd part is true.
Since part (a) is true for all records, they all get returned when the text
box is null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I used your suggestion about using a form. It works fine. My form is
based on a table. My criteria is based on States. We have 6 states that
we are summing data on: AL, AZ, CO, FL, NM, TX. So in my States table
(Fields: State, StateAbrev, StateCode (all text fields)) I have 6
records, 1 for each state. So with the form open, I can navigate to the
state I want to get data for and then run my query.

The problem I'm having is that at times I want to get data on all states.
I was trying to enter another record in the States table to accomplish
this: State = All; StateAbrev = ALL; StateCode = *. I could not get this
to work.

I simplified the query into a single select query (eliminated all the
UNIONS) and changed the criteria to a parameter so it will ask me to enter
the state code. I've tried *, "*", Like *, Like "*", 0*, "0*", Like 0*,
Like "0*". Nothing seems to work--all of these combos returned a
recordset with 0 records (it will return a recordset with 1 record when I
enter an actuall state code--it should return a recordset with 1 record in
it but with balances for all states sumed). (the 0* combinations above
are because the StateCode field for each state is 4 numeric characters
(text field, remember) and the first 2 characters are 0's (zeros))

Is there some kind of wild card combination that I can enter into my
Table/Form record (or parameter) that will allow me to return all states?

Thanks again for all of your help,

Conan Kelly

Try declaring your PARAMETERS on the first line of the query, i.e. before
the first SELECT.

With a normal query (not UNION), you can get Access to generate this line
for you by choosing Parameters on the Query menu. Then switch to SQL View
(View menu), and copy the example you see there.

An alternative would be to use a form where the user enters the values,
and then refer to that same location in each query, e.g.:
[Forms].[Form1].[Text0]

I'm going to try to create a UNION query that will sum balances from 16
other queries. In each of these queries is data for 2 states. I would
like to have the query to ask the user for which state they want data
only once at the begining of the query and then pass that parameter on
to each of the other queries in each part of the UNION query.

Is this possible? Please post some sample SQL text on how to do this.
I have no idea on where to begin. Can I have variables in SQL text that
I can assign a value to?

 

[Guest]

I had a similar problem and resolved it with Like "*" & "0" & "*".

Allen,

Thanks for your help.

I used your suggestion about using a form. It works fine. My form is based on a table. My criteria is based on States. We have 6
states that we are summing data on: AL, AZ, CO, FL, NM, TX. So in my States table (Fields: State, StateAbrev, StateCode (all text
fields)) I have 6 records, 1 for each state. So with the form open, I can navigate to the state I want to get data for and then run
my query.

The problem I'm having is that at times I want to get data on all states. I was trying to enter another record in the States table
to accomplish this: State = All; StateAbrev = ALL; StateCode = *. I could not get this to work.

I simplified the query into a single select query (eliminated all the UNIONS) and changed the criteria to a parameter so it will ask
me to enter the state code. I've tried *, "*", Like *, Like "*", 0*, "0*", Like 0*, Like "0*". Nothing seems to work--all of these
combos returned a recordset with 0 records (it will return a recordset with 1 record when I enter an actuall state code--it should
return a recordset with 1 record in it but with balances for all states sumed). (the 0* combinations above are because the
StateCode field for each state is 4 numeric characters (text field, remember) and the first 2 characters are 0's (zeros))

Is there some kind of wild card combination that I can enter into my Table/Form record (or parameter) that will allow me to return
all states?

Thanks again for all of your help,

Conan Kelly

Try declaring your PARAMETERS on the first line of the query, i.e. before the first SELECT.

With a normal query (not UNION), you can get Access to generate this line for you by choosing Parameters on the Query menu. Then
switch to SQL View (View menu), and copy the example you see there.

An alternative would be to use a form where the user enters the values, and then refer to that same location in each query, e.g.:
[Forms].[Form1].[Text0]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm going to try to create a UNION query that will sum balances from 16 other queries. In each of these queries is data for 2
states. I would like to have the query to ask the user for which state they want data only once at the begining of the query and
then pass that parameter on to each of the other queries in each part of the UNION query.

Is this possible? Please post some sample SQL text on how to do this. I have no idea on where to begin. Can I have variables
in SQL text that I can assign a value to?