I have a report that is used to guide readers thru a diagram that uses sequential letters to indicate specific auto crash data. I need the report to alphabetically "number" itself when run and start over at "A" with each new group. Right now it is numbering fine, but is not starting over with each new group and I have no clue how to make it letter instead of numbers.
"auto number" but use alphabet
- Thread starter kfarwell
- Start date
kfarwell said:
Set your 'numbering' textbox (which I assume is unbound) running sum property to 'Over Group' as opposed to 'Over All' - this will restart the numbering at the start of each group.
Next set this same textbox visible property to No.
Thirdly create a table with a primary key (integer) and letter field like:
PK Letter
1 A
2 B
3 C
etc.
Now create an additional textbox in the detail section of the report and use the dlookup function to return the corresponding letter from your new table (using your original numbering textbox as the criteria for the lookup).
Almost there!
Thanks R6Flyer. I am still having a bit of a problem in that my return letter comes up all "A".
I created a table called Pick Letter, and set a numeric primary key. The two fields are ID and Letter.
In my new textbox in the detail section I put the following in the criteria:
DLookup("[Letter]","Pick Letter",[Text99]) "Text 99 is the original textbox."
It's bound to be something silly!
Thanks R6Flyer. I am still having a bit of a problem in that my return letter comes up all "A".
I created a table called Pick Letter, and set a numeric primary key. The two fields are ID and Letter.
In my new textbox in the detail section I put the following in the criteria:
DLookup("[Letter]","Pick Letter",[Text99]) "Text 99 is the original textbox."
It's bound to be something silly!
kfarwell said:Thanks R6Flyer. I am still having a bit of a problem in that my return letter comes up all "A".
I created a table called Pick Letter, and set a numeric primary key. The two fields are ID and Letter.
In my new textbox in the detail section I put the following in the criteria:
DLookup("[Letter]","Pick Letter",[Text99]) "Text 99 is the original textbox."
It's bound to be something silly!
Alter your lookup to read as:
DLookup("[Letter]","Pick Letter","[ID]=" & [Text99])
or alternatively, if that doesn't work try:
DLookup("[Letter]","Pick Letter","[ID]=[Text99]")
kfarwell said:
Off the top of my head, try:
DLookup("[Letter]","Pick Letter","[ID]=" & Me.[Text99])
If that still errors, could you export the report, query and tables into a new database, compact and zip it and post it here. Either A97 or A2000 format will be OK.
I am about to leave work now but can take a look at it tonight for you.
kfarwell,
Did you manage to get the report working? I have just tried a test using
=DLookUp("[Letter]","Pick Letter","[ID]=" & [Text99])
and didn't get any sort of error message. The output was as you required, i.e. the corresponding letter from the newly created table.
Double-check all of your field and table names if you are still getting an error using the above as the control source for your unbound textbox.
Did you manage to get the report working? I have just tried a test using
=DLookUp("[Letter]","Pick Letter","[ID]=" & [Text99])
and didn't get any sort of error message. The output was as you required, i.e. the corresponding letter from the newly created table.
Double-check all of your field and table names if you are still getting an error using the above as the control source for your unbound textbox.
IT"S WORKING!!!
Thanks so much for being patient and working with me. I have learned a great deal!
Finally! I went back as you suggested and somewhere along the way, while I was changing and trying different things, I left out the =1 command in my unbound text box [text99]. It works like a charm.kfarwell said:I haven't had a chance yet, there was a family emergency. I will let you know as soon as I have a chance to try it. Thanks so much for your help.
Thanks so much for being patient and working with me. I have learned a great deal!
