Report with subreport and input parameters

S

Stephanie

Hi. I've had great luck with creating a query that asks for user input (say,
letting the user select the state), and then having reports run based on that
user input. But now I have a more complicated situation and I'm wondering if
I can do something similar.

I have to ask a query question to lead up to my report question: I am using
a union query. Which seems to be perhaps one of my issues. The union query
is fine until I let the user select an input. I have a field that is
"unioned" to contain both -1 and 0 values (for yes/no, but based on two
different fields from two different queries). My user input looks like this:
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])

(FindLabel is my State selection). The union query itself only asks me to
select from FindLabel once, however my query brings back essentially
duplicate rows (one for 0, one for no-1) of each record, as I have had to
place the WHERE criteria in both sections of the union query.

Is there a way to let the user select the State in my union query without
the duplication issue?
 
A

Allen Browne

Stephanie, try declaring your the text box value as a parameter in your
query.

The union query will then start:
PARAMETERS [Find]![LabelsParam]![FindLabel] Text ( 255 );
SELECT ...

BTW, I'm assuming that your line:
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])
is part of the WHERE clause of each SELECT statement.
If it is just one of the fields in the SELECT clause, then it will return
the True and False values.
 
S

Stephanie

Allen, always nice to see that you've replied! Thanks.

((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) is part
of my WHERE clause.

I'm still not getting what I expect to see, perhaps you can tell me where
I've gone astray. What does Text (255) mean?

First I tried:
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
UNION
SELECT Volunteering.VolunteeringID...

Both SELECT statements without
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) in the
WHERE clauses.

I was prompted for State once, but the union query didn't bring back the
correct State- it brought back all records (once).

So then I tried
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
WHERE ... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);
UNION
SELECT Volunteering.VolunteeringID...
WHERE... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);

Which prompted me for State twice, brought back the correct State, but
listed duplicate records (one each for 0 and -1 on my "unioned" field), which
is exactly what I was getting without using the PARAMETERS statement.

Thanks for the help!

Allen Browne said:
Stephanie, try declaring your the text box value as a parameter in your
query.

The union query will then start:
PARAMETERS [Find]![LabelsParam]![FindLabel] Text ( 255 );
SELECT ...

BTW, I'm assuming that your line:
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])
is part of the WHERE clause of each SELECT statement.
If it is just one of the fields in the SELECT clause, then it will return
the True and False values.

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

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

Stephanie said:
Hi. I've had great luck with creating a query that asks for user input
(say,
letting the user select the state), and then having reports run based on
that
user input. But now I have a more complicated situation and I'm wondering
if
I can do something similar.

I have to ask a query question to lead up to my report question: I am
using
a union query. Which seems to be perhaps one of my issues. The union
query
is fine until I let the user select an input. I have a field that is
"unioned" to contain both -1 and 0 values (for yes/no, but based on two
different fields from two different queries). My user input looks like
this:
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])

(FindLabel is my State selection). The union query itself only asks me to
select from FindLabel once, however my query brings back essentially
duplicate rows (one for 0, one for no-1) of each record, as I have had to
place the WHERE criteria in both sections of the union query.

Is there a way to let the user select the State in my union query without
the duplication issue?
Click to expand...
Click to expand...
 
A

Allen Browne

Okay, what is:
[Find]![LabelsParam]![FindLabel]

Should that read:
[Forms].[LabelsParam].[FindLabel]
where LabelsParam is the name of the form, and FindLabel is the name of the
text box?

You will need the "Forms" bit.
If there is a subform invovled here, you will also need the .Form bit
between the subform name and the control name, as explained here:
http://allenbrowne.com/casu-04.html

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

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

Stephanie said:
Allen, always nice to see that you've replied! Thanks.

((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) is
part
of my WHERE clause.

I'm still not getting what I expect to see, perhaps you can tell me where
I've gone astray. What does Text (255) mean?

First I tried:
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
UNION
SELECT Volunteering.VolunteeringID...

Both SELECT statements without
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) in
the
WHERE clauses.

I was prompted for State once, but the union query didn't bring back the
correct State- it brought back all records (once).

So then I tried
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
WHERE ... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);
UNION
SELECT Volunteering.VolunteeringID...
WHERE... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);

Which prompted me for State twice, brought back the correct State, but
listed duplicate records (one each for 0 and -1 on my "unioned" field),
which
is exactly what I was getting without using the PARAMETERS statement.

Thanks for the help!

Allen Browne said:
Stephanie, try declaring your the text box value as a parameter in your
query.

The union query will then start:
PARAMETERS [Find]![LabelsParam]![FindLabel] Text ( 255 );
SELECT ...

BTW, I'm assuming that your line:
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])
is part of the WHERE clause of each SELECT statement.
If it is just one of the fields in the SELECT clause, then it will return
the True and False values.

Stephanie said:
Hi. I've had great luck with creating a query that asks for user input
(say,
letting the user select the state), and then having reports run based
on
that
user input. But now I have a more complicated situation and I'm
wondering
if
I can do something similar.

I have to ask a query question to lead up to my report question: I am
using
a union query. Which seems to be perhaps one of my issues. The union
query
is fine until I let the user select an input. I have a field that is
"unioned" to contain both -1 and 0 values (for yes/no, but based on two
different fields from two different queries). My user input looks like
this:
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])

(FindLabel is my State selection). The union query itself only asks me
to
select from FindLabel once, however my query brings back essentially
duplicate rows (one for 0, one for no-1) of each record, as I have had
to
place the WHERE criteria in both sections of the union query.

Is there a way to let the user select the State in my union query
without
the duplication issue?
Click to expand...
Click to expand...
Click to expand...
 
S

Stephanie

Sorry! I have fixed all references to [Forms].[LabelsParam].[FindLabel]
which is correct. No subform involved. Here's the results:

method1:
PARAMETERS [Forms]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
UNION
SELECT Volunteering.VolunteeringID...

Both SELECT statements without
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]) in the
WHERE clauses.

I was prompted for State once, but the union query didn't bring back the
correct State- it brought back all records twice (once for 0 once for -1).

So then I tried
method2:
PARAMETERS [Forms]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
WHERE ... AND
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]);
UNION
SELECT Volunteering.VolunteeringID...
WHERE... AND
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]);

Which prompted me for State twice, brought back the correct State, but
listed duplicate records (one each for 0 and -1 on my "unioned" field), which
is exactly what I was getting without using the PARAMETERS statement.

I'm guessing that method1 (just using PARAMETERS once with no other
reference to [Forms]![LabelsParam]![FindLabels] is correct. True?

I'd appreciate any suggestions.... Thanks.

Allen Browne said:
Okay, what is:
[Find]![LabelsParam]![FindLabel]

Should that read:
[Forms].[LabelsParam].[FindLabel]
where LabelsParam is the name of the form, and FindLabel is the name of the
text box?

You will need the "Forms" bit.
If there is a subform invovled here, you will also need the .Form bit
between the subform name and the control name, as explained here:
http://allenbrowne.com/casu-04.html

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

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

Stephanie said:
Allen, always nice to see that you've replied! Thanks.

((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) is
part
of my WHERE clause.

I'm still not getting what I expect to see, perhaps you can tell me where
I've gone astray. What does Text (255) mean?

First I tried:
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
UNION
SELECT Volunteering.VolunteeringID...

Both SELECT statements without
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) in
the
WHERE clauses.

I was prompted for State once, but the union query didn't bring back the
correct State- it brought back all records (once).

So then I tried
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
WHERE ... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);
UNION
SELECT Volunteering.VolunteeringID...
WHERE... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);

Which prompted me for State twice, brought back the correct State, but
listed duplicate records (one each for 0 and -1 on my "unioned" field),
which
is exactly what I was getting without using the PARAMETERS statement.

Thanks for the help!

Allen Browne said:
Stephanie, try declaring your the text box value as a parameter in your
query.

The union query will then start:
PARAMETERS [Find]![LabelsParam]![FindLabel] Text ( 255 );
SELECT ...

BTW, I'm assuming that your line:
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])
is part of the WHERE clause of each SELECT statement.
If it is just one of the fields in the SELECT clause, then it will return
the True and False values.

Hi. I've had great luck with creating a query that asks for user input
(say,
letting the user select the state), and then having reports run based
on
that
user input. But now I have a more complicated situation and I'm
wondering
if
I can do something similar.

I have to ask a query question to lead up to my report question: I am
using
a union query. Which seems to be perhaps one of my issues. The union
query
is fine until I let the user select an input. I have a field that is
"unioned" to contain both -1 and 0 values (for yes/no, but based on two
different fields from two different queries). My user input looks like
this:
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])

(FindLabel is my State selection). The union query itself only asks me
to
select from FindLabel once, however my query brings back essentially
duplicate rows (one for 0, one for no-1) of each record, as I have had
to
place the WHERE criteria in both sections of the union query.

Is there a way to let the user select the State in my union query
without
the duplication issue?
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Stephanie, you must have some further issues here.

Once you get the references correct, you will not be prompted for *any*
parameters if the form is open.

Also, with or without the parameter declared, you will prompted once only if
the query is correctly designed.

I have just tested those both statements here, so keep working on it until
you get it right.

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

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

Stephanie said:
Sorry! I have fixed all references to [Forms].[LabelsParam].[FindLabel]
which is correct. No subform involved. Here's the results:

method1:
PARAMETERS [Forms]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
UNION
SELECT Volunteering.VolunteeringID...

Both SELECT statements without
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]) in
the
WHERE clauses.

I was prompted for State once, but the union query didn't bring back the
correct State- it brought back all records twice (once for 0 once for -1).

So then I tried
method2:
PARAMETERS [Forms]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
WHERE ... AND
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]);
UNION
SELECT Volunteering.VolunteeringID...
WHERE... AND
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]);

Which prompted me for State twice, brought back the correct State, but
listed duplicate records (one each for 0 and -1 on my "unioned" field),
which
is exactly what I was getting without using the PARAMETERS statement.

I'm guessing that method1 (just using PARAMETERS once with no other
reference to [Forms]![LabelsParam]![FindLabels] is correct. True?

I'd appreciate any suggestions.... Thanks.

Allen Browne said:
Okay, what is:
[Find]![LabelsParam]![FindLabel]

Should that read:
[Forms].[LabelsParam].[FindLabel]
where LabelsParam is the name of the form, and FindLabel is the name of
the
text box?

You will need the "Forms" bit.
If there is a subform invovled here, you will also need the .Form bit
between the subform name and the control name, as explained here:
http://allenbrowne.com/casu-04.html

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

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

Stephanie said:
Allen, always nice to see that you've replied! Thanks.

((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) is
part
of my WHERE clause.

I'm still not getting what I expect to see, perhaps you can tell me
where
I've gone astray. What does Text (255) mean?

First I tried:
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
UNION
SELECT Volunteering.VolunteeringID...

Both SELECT statements without
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) in
the
WHERE clauses.

I was prompted for State once, but the union query didn't bring back
the
correct State- it brought back all records (once).

So then I tried
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
WHERE ... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);
UNION
SELECT Volunteering.VolunteeringID...
WHERE... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);

Which prompted me for State twice, brought back the correct State, but
listed duplicate records (one each for 0 and -1 on my "unioned" field),
which
is exactly what I was getting without using the PARAMETERS statement.

Thanks for the help!

:

Stephanie, try declaring your the text box value as a parameter in
your
query.

The union query will then start:
PARAMETERS [Find]![LabelsParam]![FindLabel] Text ( 255 );
SELECT ...

BTW, I'm assuming that your line:

((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])
is part of the WHERE clause of each SELECT statement.
If it is just one of the fields in the SELECT clause, then it will
return
the True and False values.

Hi. I've had great luck with creating a query that asks for user
input
(say,
letting the user select the state), and then having reports run
based
on
that
user input. But now I have a more complicated situation and I'm
wondering
if
I can do something similar.

I have to ask a query question to lead up to my report question: I
am
using
a union query. Which seems to be perhaps one of my issues. The
union
query
is fine until I let the user select an input. I have a field that
is
"unioned" to contain both -1 and 0 values (for yes/no, but based on
two
different fields from two different queries). My user input looks
like
this:
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])

(FindLabel is my State selection). The union query itself only asks
me
to
select from FindLabel once, however my query brings back essentially
duplicate rows (one for 0, one for no-1) of each record, as I have
had
to
place the WHERE criteria in both sections of the union query.

Is there a way to let the user select the State in my union query
without
the duplication issue?
Click to expand...
Click to expand...
Click to expand...
 
S

Stephanie

Allen,
Thanks for pointing out that I had other query issues. I ditched the union
query and have a simpler approach that is working correctly. I have used:
WHERE...AND
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabels])) which
prompts me for a State and then limits the records based on that State.

When I tried to use
PARAMETERS [Forms]![LabelsParam]![FindLabel] Text ( 255 );
the query prompts me for the State, but then returns all records. I don't
know why this approach isn't working, but I'm comfortable using
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabels])) and
it's working correctly.

Now I'm ready to ask my Report question. I've never worked with a subreport
before. My subreport has code:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "LabelsParam", , , , , acDialog
If Not IsLoaded("LabelsParam") Then
Cancel = True
End If
End Sub

This works well- when I run the subreport (srptProgram). LabelsParam opens,
I select a State and the subreport returns the correct information. This
query and report code method is what I've used for all of my reports, and
figured it would work on a subreport, which it does.

On my main report (Program Facilities Calendar) I have no code referencing
LabelsParam. Which I believe is correct since the LabelsParam code is on the
subreport. When I run the main report, I'm prompted for the State, I select
a State and then I'm prompted for a State over and over again. I'm guessing
for every record returned by the query, then the calendar report grudgingly
opens and lists the correct info.

Do I have my query, subreport, and report code set up correctly to use an
input parameter? Why does the report keep promting for State over and over?
Thanks for your help!

Allen Browne said:
Stephanie, you must have some further issues here.

Once you get the references correct, you will not be prompted for *any*
parameters if the form is open.

Also, with or without the parameter declared, you will prompted once only if
the query is correctly designed.

I have just tested those both statements here, so keep working on it until
you get it right.

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

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

Stephanie said:
Sorry! I have fixed all references to [Forms].[LabelsParam].[FindLabel]
which is correct. No subform involved. Here's the results:

method1:
PARAMETERS [Forms]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
UNION
SELECT Volunteering.VolunteeringID...

Both SELECT statements without
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]) in
the
WHERE clauses.

I was prompted for State once, but the union query didn't bring back the
correct State- it brought back all records twice (once for 0 once for -1).

So then I tried
method2:
PARAMETERS [Forms]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
WHERE ... AND
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]);
UNION
SELECT Volunteering.VolunteeringID...
WHERE... AND
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]);

Which prompted me for State twice, brought back the correct State, but
listed duplicate records (one each for 0 and -1 on my "unioned" field),
which
is exactly what I was getting without using the PARAMETERS statement.

I'm guessing that method1 (just using PARAMETERS once with no other
reference to [Forms]![LabelsParam]![FindLabels] is correct. True?

I'd appreciate any suggestions.... Thanks.

Allen Browne said:
Okay, what is:
[Find]![LabelsParam]![FindLabel]

Should that read:
[Forms].[LabelsParam].[FindLabel]
where LabelsParam is the name of the form, and FindLabel is the name of
the
text box?

You will need the "Forms" bit.
If there is a subform invovled here, you will also need the .Form bit
between the subform name and the control name, as explained here:
http://allenbrowne.com/casu-04.html

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

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

Allen, always nice to see that you've replied! Thanks.

((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) is
part
of my WHERE clause.

I'm still not getting what I expect to see, perhaps you can tell me
where
I've gone astray. What does Text (255) mean?

First I tried:
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
UNION
SELECT Volunteering.VolunteeringID...

Both SELECT statements without
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) in
the
WHERE clauses.

I was prompted for State once, but the union query didn't bring back
the
correct State- it brought back all records (once).

So then I tried
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
WHERE ... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);
UNION
SELECT Volunteering.VolunteeringID...
WHERE... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);

Which prompted me for State twice, brought back the correct State, but
listed duplicate records (one each for 0 and -1 on my "unioned" field),
which
is exactly what I was getting without using the PARAMETERS statement.

Thanks for the help!

:

Stephanie, try declaring your the text box value as a parameter in
your
query.

The union query will then start:
PARAMETERS [Find]![LabelsParam]![FindLabel] Text ( 255 );
SELECT ...

BTW, I'm assuming that your line:

((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])
is part of the WHERE clause of each SELECT statement.
If it is just one of the fields in the SELECT clause, then it will
return
the True and False values.

Hi. I've had great luck with creating a query that asks for user
input
(say,
letting the user select the state), and then having reports run
based
on
that
user input. But now I have a more complicated situation and I'm
wondering
if
I can do something similar.

I have to ask a query question to lead up to my report question: I
am
using
a union query. Which seems to be perhaps one of my issues. The
union
query
is fine until I let the user select an input. I have a field that
is
"unioned" to contain both -1 and 0 values (for yes/no, but based on
two
different fields from two different queries). My user input looks
like
this:
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])

(FindLabel is my State selection). The union query itself only asks
me
to
select from FindLabel once, however my query brings back essentially
duplicate rows (one for 0, one for no-1) of each record, as I have
had
to
place the WHERE criteria in both sections of the union query.

Is there a way to let the user select the State in my union query
without
the duplication issue?
Click to expand...
Click to expand...
Click to expand...
 
S

Stephanie

Just had a nap, and realized a big part of my problem. To recap:
I'm using WHERE... AND
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabels])) is my
query and it's working correctly.

My subreport has code:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "LabelsParam", , , , , acDialog
If Not IsLoaded("LabelsParam") Then
Cancel = True
End If
End Sub

This works well- when I run the subreport (srptProgram). LabelsParam opens,
I select a State and the subreport returns the correct information. This
query and report code method is what I've used for all of my reports, and
figured it would work on a subreport, which it does.

On my main report (Program Facilities Calendar) I have no code referencing
LabelsParam. I have 7 of the subreports on my main report. Duane H helped
me design this calendar report for each day of the week. The report works
fine, of course. Only recently did I realize that I need a separate report
based on each associated State.

When I run the main report, I'm prompted for the State, I select
a State and then I'm prompted for a State over and over again. For one
State that has 2 records, I'm prompted I think I counted 34 times (which
actually may make sense in that the calendar report is a "matrix" 7 days
across, 5 weeks down).

Any suggestions for how I can pass the parameter input once to all 7 of the
subreports on the main report?
Thanks. I know it's frustrating to help when I don't pass on all the
information at once. I appreciate it.


Allen Browne said:
Stephanie, you must have some further issues here.

Once you get the references correct, you will not be prompted for *any*
parameters if the form is open.

Also, with or without the parameter declared, you will prompted once only if
the query is correctly designed.

I have just tested those both statements here, so keep working on it until
you get it right.

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

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

Stephanie said:
Sorry! I have fixed all references to [Forms].[LabelsParam].[FindLabel]
which is correct. No subform involved. Here's the results:

method1:
PARAMETERS [Forms]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
UNION
SELECT Volunteering.VolunteeringID...

Both SELECT statements without
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]) in
the
WHERE clauses.

I was prompted for State once, but the union query didn't bring back the
correct State- it brought back all records twice (once for 0 once for -1).

So then I tried
method2:
PARAMETERS [Forms]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
WHERE ... AND
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]);
UNION
SELECT Volunteering.VolunteeringID...
WHERE... AND
((Organizations.OrganizationState)=[Forms]![LabelsParam]![FindLabel]);

Which prompted me for State twice, brought back the correct State, but
listed duplicate records (one each for 0 and -1 on my "unioned" field),
which
is exactly what I was getting without using the PARAMETERS statement.

I'm guessing that method1 (just using PARAMETERS once with no other
reference to [Forms]![LabelsParam]![FindLabels] is correct. True?

I'd appreciate any suggestions.... Thanks.

Allen Browne said:
Okay, what is:
[Find]![LabelsParam]![FindLabel]

Should that read:
[Forms].[LabelsParam].[FindLabel]
where LabelsParam is the name of the form, and FindLabel is the name of
the
text box?

You will need the "Forms" bit.
If there is a subform invovled here, you will also need the .Form bit
between the subform name and the control name, as explained here:
http://allenbrowne.com/casu-04.html

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

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

Allen, always nice to see that you've replied! Thanks.

((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) is
part
of my WHERE clause.

I'm still not getting what I expect to see, perhaps you can tell me
where
I've gone astray. What does Text (255) mean?

First I tried:
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
UNION
SELECT Volunteering.VolunteeringID...

Both SELECT statements without
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]) in
the
WHERE clauses.

I was prompted for State once, but the union query didn't bring back
the
correct State- it brought back all records (once).

So then I tried
PARAMETERS [Find]![LabelsParam]![FindLabels] Text ( 255 );
SELECT Volunteering.VolunteeringID...
WHERE ... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);
UNION
SELECT Volunteering.VolunteeringID...
WHERE... AND
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel]);

Which prompted me for State twice, brought back the correct State, but
listed duplicate records (one each for 0 and -1 on my "unioned" field),
which
is exactly what I was getting without using the PARAMETERS statement.

Thanks for the help!

:

Stephanie, try declaring your the text box value as a parameter in
your
query.

The union query will then start:
PARAMETERS [Find]![LabelsParam]![FindLabel] Text ( 255 );
SELECT ...

BTW, I'm assuming that your line:

((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])
is part of the WHERE clause of each SELECT statement.
If it is just one of the fields in the SELECT clause, then it will
return
the True and False values.

Hi. I've had great luck with creating a query that asks for user
input
(say,
letting the user select the state), and then having reports run
based
on
that
user input. But now I have a more complicated situation and I'm
wondering
if
I can do something similar.

I have to ask a query question to lead up to my report question: I
am
using
a union query. Which seems to be perhaps one of my issues. The
union
query
is fine until I let the user select an input. I have a field that
is
"unioned" to contain both -1 and 0 values (for yes/no, but based on
two
different fields from two different queries). My user input looks
like
this:
((Organizations.OrganizationState)=[Find]![LabelsParam]![FindLabel])

(FindLabel is my State selection). The union query itself only asks
me
to
select from FindLabel once, however my query brings back essentially
duplicate rows (one for 0, one for no-1) of each record, as I have
had
to
place the WHERE criteria in both sections of the union query.

Is there a way to let the user select the State in my union query
without
the duplication issue?
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

In the Criteria of each of the subreports, include the reference to the text
box on the form. They will all read it from the text box (assuming the form
is open).

That's assuming that the LinkChildFields/LinkMasterFields are not suitable
for limiting the subreports.
 
S

Stephanie

Thanks for the reply. I should be more clear: I have the SAME subreport 7
times on my main report. The subreport brings back occurrences by day for
the 7 days in the week.

My main report is simply built on a table that has WeekID and WeekNo. (1...5)
Across my Page Header I have 7 text boxes: txt1...txt7 (these are for the 7
days). Down the side of the Detail section I have Week1...Week5.
Each subreport has Link Child as WeekNo;DayID and Link Master as WeekNo;txt1
(....or txt7). So in my total inexperience, I believe that the
LinkChildFields/LinkMasterFields are not suitable for limiting the
subreports. True?

I suppose I could build separate queries that limit based on State, but if
it is possible to use an input parameter, I'd like to give that a shot. Do
you think with my set up that an input parameter is feasible? Thanks.
 
S

Stephanie

Allen,
I've decided to go with separate queries. Not too streamlined, but it's
working.
Thanks for all of your help- I appreciate it! Cheers!
 
Top