(Msg. 1) Posted: Wed Jul 23, 2008 7:50 am
Post subject: Parameters with mor than one crosstab query in a report. Add to elertz Archived from groups: microsoft>public>access>reports (more info?)
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?
(Msg. 2) Posted: Wed Jul 23, 2008 7:56 pm
Post subject: RE: Parameters with mor than one crosstab query in a report. Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
> 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?
(Msg. 3) Posted: Thu Jul 24, 2008 2:10 am
Post subject: RE: Parameters with mor than one crosstab query in a report. Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
"Duane Hookom" wrote:
> 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 >
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "Jomark" wrote:
>
> > 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?
(Msg. 4) Posted: Thu Jul 24, 2008 11:34 am
Post subject: RE: Parameters with mor than one crosstab query in a report. Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
"Jomark" wrote:
> 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.
>
> "Duane Hookom" wrote:
>
> > 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 > >
> > --
> > Duane Hookom
> > Microsoft Access MVP
> >
> >
> > "Jomark" wrote:
> >
> > > 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?
(Msg. 5) Posted: Thu Jul 24, 2008 12:48 pm
Post subject: RE: Parameters with mor than one crosstab query in a report. Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Try:
>=DateSerial(Forms!frmA!txtYear,4,1) And < DateSerial(Forms!frmA!txtYear+1,4,1)
--
Duane Hookom
Microsoft Access MVP
"Jomark" wrote:
> 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.
>
> "Jomark" wrote:
>
> > 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.
> >
> > "Duane Hookom" wrote:
> >
> > > 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 > > >
> > > --
> > > Duane Hookom
> > > Microsoft Access MVP
> > >
> > >
> > > "Jomark" wrote:
> > >
> > > > 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?
(Msg. 6) Posted: Fri Jul 25, 2008 7:07 am
Post subject: RE: Parameters with mor than one crosstab query in a report. Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
"Jomark" wrote:
> 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?
(Msg. 7) Posted: Fri Jul 25, 2008 9:25 am
Post subject: RE: Parameters with mor than one crosstab query in a report. Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
"Jomark" wrote:
> 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.
>
>
>
> "Jomark" wrote:
>
> > 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?
(Msg. 8) Posted: Fri Jul 25, 2008 10:55 am
Post subject: RE: Parameters with mor than one crosstab query in a report. Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
"Duane Hookom" wrote:
> 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
>
>
> "Jomark" wrote:
>
> > 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.
> >
> >
> >
> > "Jomark" wrote:
> >
> > > 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?
All times are: Eastern Time (US & Canada) (change) Goto page 1, 2
Page 1 of 2
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Search
Profile
Private Messages
Log in/Register/Password
Office Other