Grouping a Report Based on a Value

[jimphilly]

In my report, I cannot have more than 99 records in a group. I have set up a
counter but I can figure out how to force a new group and page once the
counter hits 99. Addtionally, I must label each group differently. The
first group is "A" then "B"... through "Z" and then "AA"..."ZZ" if necessary.

Thanks

 

[Jeff Boyce]

I've not run into that limitation before. Can you post a bit more about the
context in which this is happening? Version of Access? How you are putting
"records in a group"?

More specific description may lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jimphilly]

I'm sorry. I didn't explain my situation well enough. I reason why I can't
have more than 99 records in a group is not a limitation in Access, rather my
organization requires each "roster" be no greater than 99 records.

These "rosters" are based on a table. I have created an unbound text box
called "BATCH_NBR" as my counter. Currently the entire contents of the table
is one group. I have not yet defined any grouping.

Thanks again

 

[John Spencer]

You can add a page break control to the report design. In the detail
section you can use

Me.PageBreakControl.Visible = me.Batch_Nbr Mod 99 = 0

That will force a page break after the 99th, 198th, etc record

You can use a similar technique to generate the A B C etc.

UNTESTED Snippet of code

Dim lngCalcVal as Long
lngCalcVal = me.Batch_Nbr \ 99

IF LngCalcVal < 27 Then
Me.txtRosterID = Asc(lngCalcVal Mod 26 + 66)
Else
Me.txtRosterID = Asc(lngCalcVal Mod 26 + 66) & Asc(lngCalcVal\26
+66)
End if





--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[jimphilly]

John

Thanks for your answer but, unfortunately, I'm not understanding

I get an error when I run Me.PageBreakControl.Visible = Me.BATCH_NBR Mod 99
= 0. It gives me a compile error (Me.BATCH_NBR) " Method or data member not
found ". Additionally, I don't understand how the MOD operator works in this
application.

When I run the untested code snippet, I get an error informing me I can't
assign a value to this object. It's referring to the text box I'm using to
capture the roster name. I'm calling it GROUP.

This is the line of code where it stops: Me.GROUP = Asc(lngCalcVal Mod 26 +
66) .

Intelisense tells me that the variable lngCalcVal = 0 after running this
line of code: lngCalcVal = Me.BATCH_NBR \ 99.

Could you explain the ASC Function? Help says a runtime error occurs if
the string has no characters. Is this the reason this error is occurring?

 

[jimphilly]

I've finally got the code to work.......kinda

I inserted the code Me![PageBreak122].Visible = [ROSTER_NBR] Mod 99 = 0
(I changed the name of the counter to ROSTER_NBR) and now the report does
start a new page at the 100th record but it doesn't reset the counter to 1.
It continues to count 100-198, page break then 199-297 and so on.

My counter is an unbound textbox and in the control source I inserted the
following code =IIf([CurrentRecord]=1,1,1), which I found here a week or so
ago. I've tried If.....Then statements to reset the counter, but I keep
getting an error informing me that the field cannot be updated. I also
played around with the currentrecord property, but that wasn't getting me
anywhere either.

I also tried the other code I was provided, but it stops at the line:

Me.GROUP = Asc(lngCalcVal Mod 26 + 66)

It informs me that "the object cannot be updated", (referring to GROUP?). I
can't really troubleshoot the code because I don't understand the ASC
function or how the MOD property fits.

Any help you can give me would be greatly appreciated.

 

[John Spencer]

Try the following

Set the control [ROSTER_NBR] so its visible property is NO.

Add another control [LineCount] to the detail section and set its source to
= ([ROSTER_NBR] -1) Mod 99 + 1

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I've finally got the code to work.......kinda

I inserted the code Me![PageBreak122].Visible = [ROSTER_NBR] Mod 99 = 0
(I changed the name of the counter to ROSTER_NBR) and now the report does
start a new page at the 100th record but it doesn't reset the counter to
1.
It continues to count 100-198, page break then 199-297 and so on.

My counter is an unbound textbox and in the control source I inserted the
following code =IIf([CurrentRecord]=1,1,1), which I found here a week or
so
ago. I've tried If.....Then statements to reset the counter, but I keep
getting an error informing me that the field cannot be updated. I also
played around with the currentrecord property, but that wasn't getting me
anywhere either.

I also tried the other code I was provided, but it stops at the line:

Me.GROUP = Asc(lngCalcVal Mod 26 + 66)

It informs me that "the object cannot be updated", (referring to GROUP?).
I
can't really troubleshoot the code because I don't understand the ASC
function or how the MOD property fits.

Any help you can give me would be greatly appreciated.

John

Thanks for your answer but, unfortunately, I'm not understanding

I get an error when I run Me.PageBreakControl.Visible = Me.BATCH_NBR Mod
99
= 0. It gives me a compile error (Me.BATCH_NBR) " Method or data member
not
found ". Additionally, I don't understand how the MOD operator works in
this
application.

When I run the untested code snippet, I get an error informing me I can't
assign a value to this object. It's referring to the text box I'm using
to
capture the roster name. I'm calling it GROUP.

This is the line of code where it stops: Me.GROUP = Asc(lngCalcVal Mod 26
+
66) .

Intelisense tells me that the variable lngCalcVal = 0 after running this
line of code: lngCalcVal = Me.BATCH_NBR \ 99.

Could you explain the ASC Function? Help says a runtime error occurs if
the string has no characters. Is this the reason this error is
occurring?