Lettering grouped records in a report (1 Viewer)

[Padwan]

Hi,
I am attempting to "letter" records on a grouped report to look like this
a. house
b. car
d. tree
I tried tweaking the numbering version of it but it was a fail. I attempted this and only got 1 forty times instead of 1-40 this was by using a textbox and entering =1 into its control properties. Then setting its running sum format from no to "Over Group" Which it should do 1-40 but it doesn't, but the funny thing is when I changed it to "Over All" I got a number sequence sadly it doesn't reset like I want it too.

Any Ideas would be awesome.

 

[vbaInet]

You need to give us more information to go on.

* You want the sequence to restart at the beginning of a new group?
* What will happen when you've run out of alphabets and still have more records to number?

 

[Padwan]

Yes,
I need it to reset at the beginning of a each group. If it goes past z then it should start as aa, bb cc dd etc. Each group is going to have 40ish records in it.

 

[vbaInet]

To get you started, put this in the Control Source of a second textbox:

=Chr(96 + txtCount)

where txtCount is the name of the textbox that does a Running Sum over the group. Don't do a Running Sum on this textbox though.

 

[Padwan]

Did that but all I get is a through out the report when i enter 1 in the prompt

 

[vbaInet]

Enter 1 where? You said you have a textbox that does a Running Sum over the group.

 

[Padwan]

I do with a contol source of "=1"but when I made the second text box with the =Chr(96 + txtCount) and ran the report it prompts me with a parameter with txtCount as its name and if I don't enter a varible in it I get #ERROR when the report opens up and if I do enter a variable lets say 1 it returns me with the letter "a" all the way down the report.

 

[vbaInet]

=Chr(96 + txtCount)

where txtCount is the name of the textbox that does a Running Sum over the group.

Please re-read this part.

The textbox where you put =1 in the Control Source, change the name to txtCount.

 

[Padwan]

OK did that. Apparently you have to change its name in the property sheet and not just change the label name. Prompt went away and it sorta works now but once it it passes z it displays other weird symbols and it only works when I change the running sum to over all. I may have to check my grouping on the report I have it extremely jerry rigged as it is because I have the detail grouped on a name which would explain why its only displaying one letter per row,Or find some vba to force it to stop after it ends that group.

 

[vbaInet]

OK did that. Apparently you have to change its name in the property sheet and not just change the label name.

It's really not apparent. I was expecting you knew this already. Basics of Access, worth reading up.

Prompt went away and it sorta works now but once it it passes z it displays other weird symbols and it only works when I change the running sum to over all.

It will also work when it's set to Over Group. Upload a test db, let me see what you're doing.

 

[Padwan]

working on that now It may be a while though

 

[vbaInet]

Yep, that's fine. I'll pick it up when I can.

 

[Padwan]

here is the sample when you launch the report you will get 3 prompts
the first one just type in your name
the second one type in 1
third one type in 3
the last 2 have to do with the page numbering. There is some vba involved with this report as well feel free to check it out if you like.

Thanks in advance

 

[vbaInet]

You are currently grouping on 4 fields. You will need to revise that or else the Over Group won't work. I would imagine some of those fields just require a Sort and not a Group.

Once you've sorted that out and made sure Over Group is working correctly, I will tell you how to proceed.

 

[Padwan]

Done and it worked. Uploading new copy as well. Now to figure out how to get it to display aa, bb, cc, after the letter z then this young access Jedi will be happy. How may I proceed next....other than the cosmetics of course.

 

[vbaInet]

Padwan, you can now use this:

=String((txtCount \ 27) + 1, Chr(96 + (txtCount - (26 * ((txtCount \ 27))))))

You don't need to change anything.

Oh, by the way I didn't look at your db. I would expect you have sorted out what I pointed out in my last post.

 

[Padwan]

Thanks that did the trick!

 

[vbaInet]

Happy to help!