If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Stuck on group header expression
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. |
#2
|
|||
|
|||
Stuck on group header 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 Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JD" wrote in message news 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. |
#3
|
|||
|
|||
Stuck on group header expression
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" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JD" wrote in message news 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. |
#4
|
|||
|
|||
Stuck on group header expression
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JD" wrote in message ... 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" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JD" wrote in message news 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. |
#5
|
|||
|
|||
Stuck on group header expression
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" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JD" wrote in message ... 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" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JD" wrote in message news 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. |
Thread Tools | |
Display Modes | |
|
|