Parameters with mor than one crosstab query in a report.

[Jomark]

I have a report based on 3 separate crosstab queries based on the same table.
one crosstab query is used to sum the results of one of the queries and does
not contain any criteria.This is displayed in the report footer.
the other two crosstab queries are identical in structure but provide
different results.
A subreport based on one of the queries is included in the page footer
both of these queries contain identical date criteria that generates a
parameter for input of the year value. both parameters have been entered into
the respective parameter query menus.

When I run the report I am required to enter the parameter value 3 times and
the report then produces the correct results.

My question is
is there a way to only enter the parameter value once?

 

[Duane Hookom]

IMHO, don't ever use parameter prompts in applications. It's just not good
user interface. http://www.tek-tips.com/faqs.cfm?fid=6763

 

[Jomark]

I realise that from reading other posts and that a form should be used and
referenced in the query.
I will give it a try
If I get stuck I will post again.
I have now reduced the report to two crosstab queries.

 

[Jomark]

I have created a form and inserted criteria in the query to reference the
text box on the form. I want to just enter the year in the text box to
provide the range


my query expressesion is" >=DateSerial([StartYear],4,1) And
<DateSerial([StartYear]+1,4,1) "

how do I reference this expression?

I am stuck as to go from here to make it work.

 

[Duane Hookom]

Try:

=DateSerial(Forms!frmA!txtYear,4,1) And < DateSerial(Forms!frmA!txtYear+1,4,1)

--
Duane Hookom
Microsoft Access MVP

I have created a form and inserted criteria in the query to reference the
text box on the form. I want to just enter the year in the text box to
provide the range


my query expressesion is" >=DateSerial([StartYear],4,1) And
<DateSerial([StartYear]+1,4,1) "

how do I reference this expression?

I am stuck as to go from here to make it work.

I realise that from reading other posts and that a form should be used and
referenced in the query.
I will give it a try
If I get stuck I will post again.
I have now reduced the report to two crosstab queries.

 

[Jomark]

I am becoming a bit of a pain
I have totally confused myself as to what I have been trying to do so I will
start again.
Both expressions work on their own when entered into the QBE criteria grid
although it requires the year to be entered four times. (Twice for the
report and twice for the embedded sub report)

This is what I have done
created a form frmA with a text box named txtYear and a button to open the
report.

In the QBE grid of the crosstab queries I entered the following
[Forms]![frmA]![txtYear] for the date field.

My problem is where and how do I enter the expression

=DateSerial(Forms!frmA!txtYear,4,1) And < DateSerial(Forms!frmA!txtYear+1,4,1) on the form to make this work.

 

[Duane Hookom]

This solution assumes you have created a form named "frmA" (better yet,
provide your own name) with a text box named "txtYear". You would have the
form opened and a year entered prior running the report.
--
Duane Hookom
Microsoft Access MVP

I am becoming a bit of a pain
I have totally confused myself as to what I have been trying to do so I will
start again.
Both expressions work on their own when entered into the QBE criteria grid
although it requires the year to be entered four times. (Twice for the
report and twice for the embedded sub report)

This is what I have done
created a form frmA with a text box named txtYear and a button to open the
report.

In the QBE grid of the crosstab queries I entered the following
[Forms]![frmA]![txtYear] for the date field.

My problem is where and how do I enter the expression

=DateSerial(Forms!frmA!txtYear,4,1) And < DateSerial(Forms!frmA!txtYear+1,4,1) on the form to make this work.

I have a report based on 3 separate crosstab queries based on the same table.
one crosstab query is used to sum the results of one of the queries and does
not contain any criteria.This is displayed in the report footer.
the other two crosstab queries are identical in structure but provide
different results.
A subreport based on one of the queries is included in the page footer
both of these queries contain identical date criteria that generates a
parameter for input of the year value. both parameters have been entered into
the respective parameter query menus.

When I run the report I am required to enter the parameter value 3 times and
the report then produces the correct results.

My question is
is there a way to only enter the parameter value once?

 

[Jomark]

I have already created this form as stated in my previous post.

what i need to know is

where and how do I enter the expression

=DateSerial(Forms!frmA!txtYear,4,1) And <

DateSerial(Forms!frmA!txtYear+1,4,1) on the form to make this work.

In the QBE grid of the crosstab queries I entered the following

[Forms]![frmA]![txtYear] for the date field that I am using

This solution assumes you have created a form named "frmA" (better yet,
provide your own name) with a text box named "txtYear". You would have the
form opened and a year entered prior running the report.
--
Duane Hookom
Microsoft Access MVP

I am becoming a bit of a pain
I have totally confused myself as to what I have been trying to do so I will
start again.
Both expressions work on their own when entered into the QBE criteria grid
although it requires the year to be entered four times. (Twice for the
report and twice for the embedded sub report)

This is what I have done
created a form frmA with a text box named txtYear and a button to open the
report.

In the QBE grid of the crosstab queries I entered the following
[Forms]![frmA]![txtYear] for the date field.

My problem is where and how do I enter the expression

=DateSerial(Forms!frmA!txtYear,4,1) And < DateSerial(Forms!frmA!txtYear+1,4,1) on the form to make this work.

I have a report based on 3 separate crosstab queries based on the same table.
one crosstab query is used to sum the results of one of the queries and does
not contain any criteria.This is displayed in the report footer.
the other two crosstab queries are identical in structure but provide
different results.
A subreport based on one of the queries is included in the page footer
both of these queries contain identical date criteria that generates a
parameter for input of the year value. both parameters have been entered into
the respective parameter query menus.

When I run the report I am required to enter the parameter value 3 times and
the report then produces the correct results.

My question is
is there a way to only enter the parameter value once?

 

[Jomark]

I have finally solved my problem after looking for clues in your samples and
some other databases and it now works.
thanks for your help

This solution assumes you have created a form named "frmA" (better yet,
provide your own name) with a text box named "txtYear". You would have the
form opened and a year entered prior running the report.
--
Duane Hookom
Microsoft Access MVP

I am becoming a bit of a pain
I have totally confused myself as to what I have been trying to do so I will
start again.
Both expressions work on their own when entered into the QBE criteria grid
although it requires the year to be entered four times. (Twice for the
report and twice for the embedded sub report)

This is what I have done
created a form frmA with a text box named txtYear and a button to open the
report.

In the QBE grid of the crosstab queries I entered the following
[Forms]![frmA]![txtYear] for the date field.

My problem is where and how do I enter the expression

=DateSerial(Forms!frmA!txtYear,4,1) And < DateSerial(Forms!frmA!txtYear+1,4,1) on the form to make this work.

I have a report based on 3 separate crosstab queries based on the same table.
one crosstab query is used to sum the results of one of the queries and does
not contain any criteria.This is displayed in the report footer.
the other two crosstab queries are identical in structure but provide
different results.
A subreport based on one of the queries is included in the page footer
both of these queries contain identical date criteria that generates a
parameter for input of the year value. both parameters have been entered into
the respective parameter query menus.

When I run the report I am required to enter the parameter value 3 times and
the report then produces the correct results.

My question is
is there a way to only enter the parameter value once?