Stuck on group header expression

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

I am trying to group on detail record counts that are multiplies of 5. I
want to print a header line and then 5 records, a header line and then 5
records, etc. I added a text box (name = txtLineNo) to the detail line with
a control source = 1 and the property of an over all running sum. I have a
second text box (name = txtGroupNo) with a control source =(txtLineNo+4)\5
and the value of that control for the first 5 records = 1 and for the second
5 records = 2. So I have been trying every way I can think of to reference
the txtGroupNo in the group on expression - without any success.

I know I can change my query to include those txtGroupNo values and the
group on will work fine when set to a control source field. However, I have
to believe there is a way for the group on to use an expression to reference
an unbound control in the detail line. But I am lost for a next step to try.

Any suggestions would be greatly appreciated.
Thank you very much.
--jd

p.s. I tried to count records in a group-on expression but got an error
about not being able to use aggregate functions in the expression.
 
Try setting the Control Source of the 2nd text box like this:
=IIf([txtLineNo] Mod 5 = 1, "Header row", Null)

That will print a header for the first record, and every 5 records after
that.
 
Allen, thank you for the response.
I made the change and when I print preview the report I see the 2nd text box
(txtGroupNo) is allways null except for the 1st record and every 5th record
thereafter just as you said. However, I do not understand how to get
GroupHeader0 to reference txtGroupNo as the group-on value. I cannot select
txtGroupNo as the group-on field because it does not show in the list. The
other choice is to group on expression. When I set the expression to
=[txtGroupNo], Access asks me to enter the parameter value. If I use the
full reference of =[Reports]![rptTest]![txtGroupNo], then the report runs but
the GroupHeader0 only prints once. Any further suggestions?
Thanks.
--jd


Allen Browne said:
Try setting the Control Source of the 2nd text box like this:
=IIf([txtLineNo] Mod 5 = 1, "Header row", Null)

That will print a header for the first record, and every 5 records after
that.

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

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

JD said:
I am trying to group on detail record counts that are multiplies of 5. I
want to print a header line and then 5 records, a header line and then 5
records, etc. I added a text box (name = txtLineNo) to the detail line
with
a control source = 1 and the property of an over all running sum. I have
a
second text box (name = txtGroupNo) with a control source =(txtLineNo+4)\5
and the value of that control for the first 5 records = 1 and for the
second
5 records = 2. So I have been trying every way I can think of to
reference
the txtGroupNo in the group on expression - without any success.

I know I can change my query to include those txtGroupNo values and the
group on will work fine when set to a control source field. However, I
have
to believe there is a way for the group on to use an expression to
reference
an unbound control in the detail line. But I am lost for a next step to
try.

Any suggestions would be greatly appreciated.
Thank you very much.
--jd

p.s. I tried to count records in a group-on expression but got an error
about not being able to use aggregate functions in the expression.
Click to expand...
Click to expand...
 
You can't use a calculated text box as a value in the Sorting'n'Grouping
box.

That's because of the timing: Access groups first, then populates the text
boxes. Ergo, a text box cannot define the grouping.

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

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

JD said:
Allen, thank you for the response.
I made the change and when I print preview the report I see the 2nd text
box
(txtGroupNo) is allways null except for the 1st record and every 5th
record
thereafter just as you said. However, I do not understand how to get
GroupHeader0 to reference txtGroupNo as the group-on value. I cannot
select
txtGroupNo as the group-on field because it does not show in the list.
The
other choice is to group on expression. When I set the expression to
=[txtGroupNo], Access asks me to enter the parameter value. If I use the
full reference of =[Reports]![rptTest]![txtGroupNo], then the report runs
but
the GroupHeader0 only prints once. Any further suggestions?
Thanks.
--jd


Allen Browne said:
Try setting the Control Source of the 2nd text box like this:
=IIf([txtLineNo] Mod 5 = 1, "Header row", Null)

That will print a header for the first record, and every 5 records after
that.

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

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

JD said:
I am trying to group on detail record counts that are multiplies of 5.
I
want to print a header line and then 5 records, a header line and then
5
records, etc. I added a text box (name = txtLineNo) to the detail line
with
a control source = 1 and the property of an over all running sum. I
have
a
second text box (name = txtGroupNo) with a control source
=(txtLineNo+4)\5
and the value of that control for the first 5 records = 1 and for the
second
5 records = 2. So I have been trying every way I can think of to
reference
the txtGroupNo in the group on expression - without any success.

I know I can change my query to include those txtGroupNo values and the
group on will work fine when set to a control source field. However, I
have
to believe there is a way for the group on to use an expression to
reference
an unbound control in the detail line. But I am lost for a next step
to
try.

Any suggestions would be greatly appreciated.
Thank you very much.
--jd

p.s. I tried to count records in a group-on expression but got an
error
about not being able to use aggregate functions in the expression.
Click to expand...
Click to expand...
Click to expand...
 
Allen, that makes sense.
I thank you for all your help.
I guess I will either change the underlying query to include a group number
or, since I don't do anything with the Group Header other than print column
titles, maybe I will leave the query alone and group on the primary key to
get a Group Header before every record but they toggle the visible property
with VBA so the Header is only visible before the first record and every 5th
record following with the mod statement you provided - that will work won't
it?
--jd


Allen Browne said:
You can't use a calculated text box as a value in the Sorting'n'Grouping
box.

That's because of the timing: Access groups first, then populates the text
boxes. Ergo, a text box cannot define the grouping.

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

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

JD said:
Allen, thank you for the response.
I made the change and when I print preview the report I see the 2nd text
box
(txtGroupNo) is allways null except for the 1st record and every 5th
record
thereafter just as you said. However, I do not understand how to get
GroupHeader0 to reference txtGroupNo as the group-on value. I cannot
select
txtGroupNo as the group-on field because it does not show in the list.
The
other choice is to group on expression. When I set the expression to
=[txtGroupNo], Access asks me to enter the parameter value. If I use the
full reference of =[Reports]![rptTest]![txtGroupNo], then the report runs
but
the GroupHeader0 only prints once. Any further suggestions?
Thanks.
--jd


Allen Browne said:
Try setting the Control Source of the 2nd text box like this:
=IIf([txtLineNo] Mod 5 = 1, "Header row", Null)

That will print a header for the first record, and every 5 records after
that.

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

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


I am trying to group on detail record counts that are multiplies of 5.
I
want to print a header line and then 5 records, a header line and then
5
records, etc. I added a text box (name = txtLineNo) to the detail line
with
a control source = 1 and the property of an over all running sum. I
have
a
second text box (name = txtGroupNo) with a control source
=(txtLineNo+4)\5
and the value of that control for the first 5 records = 1 and for the
second
5 records = 2. So I have been trying every way I can think of to
reference
the txtGroupNo in the group on expression - without any success.

I know I can change my query to include those txtGroupNo values and the
group on will work fine when set to a control source field. However, I
have
to believe there is a way for the group on to use an expression to
reference
an unbound control in the detail line. But I am lost for a next step
to
try.

Any suggestions would be greatly appreciated.
Thank you very much.
--jd

p.s. I tried to count records in a group-on expression but got an
error
about not being able to use aggregate functions in the expression.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top