conting by letter rather than number

[ArielZusya]

I'm working on a dataset which numbers each entry by number (1, 2, 3, etc.)
for one group and by letter for another group (A, B, C, ... AA, BB, CC, ...
AAA, BBB, CCC, etc.). I'd like to have the system figure out the last record
entered and detect the letters and know what should come next. That way if
the 28th record is entered (BB) it knows that the 29th record should be CC.
Is there an easy way to accomplish this using VBA? I could simply create a
table with the first 130 entries (A - ZZZZZ) but I'd rather have the system
work a bit more independantly (so in the event that there is 131 records I
don't run into trouble). Thanks!

 

[Klatuu]

Not a big problem. To find the last number entered, use the DMax function to
find the current highest value.

Then you can convert the characters to numbers using the Asc() function.
For example, Asc("A") returns 65 and Asc("Z") returns 90.

So if the current leftmost position was say "B" you can convert it to C by
x=Asc(Right(strNextNum),1)
If = 90 Then .... You have to go to the left one and change it
Else
Right(strNextNum,1) = Chr(x + 1)

 

[ArielZusya]

OK... so... that's so cool... quick question though... How do I get past 90?
91 seems to go to [ rather than to AA? I imagine that's because asc must be
returning the key mapped to that value or something like that, right? I
suppose I would have to have it do some math or something to get it to
concatonate the chr of two asc returns or something. I noticed that asc("AA")
returns 65 so I'm not clear on how to make this all work. Do I need to loop
this? Wouldn't it have been great if the two groups both used something
easier like GroupA1, GroupA2, GroupA3, etc. and GroupB1, GroupB2, GroupB3
etc. That I could do with no assistance. At least I'm learning something
new though. Any thoughts on what I should do next would be greatly
appreciated! Thanks for all your help.

 

[ArielZusya]

Argh... accidentally clicked the reply to my post rather than to your
response. Just in case it doesn't send out a notify unless the reply is in
the rigth place... here we go (guess getting enough sleep is important
*SMIRK*).

 

[Klatuu]

I can show you how to write the code, but I need to know some rules.
How many characters can be in the string?
What is the order. For example, I would expect it would go from
A - Z
AA - AZ
AB - ABZ

In your original example, you show going form BB to CC, but that doesn't
seem logical. However, it is your app and your rules, so I need to know how
the order actually is.
--
Dave Hargis, Microsoft Access MVP

OK... so... that's so cool... quick question though... How do I get past 90?
91 seems to go to [ rather than to AA? I imagine that's because asc must be
returning the key mapped to that value or something like that, right? I
suppose I would have to have it do some math or something to get it to
concatonate the chr of two asc returns or something. I noticed that asc("AA")
returns 65 so I'm not clear on how to make this all work. Do I need to loop
this? Wouldn't it have been great if the two groups both used something
easier like GroupA1, GroupA2, GroupA3, etc. and GroupB1, GroupB2, GroupB3
etc. That I could do with no assistance. At least I'm learning something
new though. Any thoughts on what I should do next would be greatly
appreciated! Thanks for all your help.

I'm working on a dataset which numbers each entry by number (1, 2, 3, etc.)
for one group and by letter for another group (A, B, C, ... AA, BB, CC, ...
AAA, BBB, CCC, etc.). I'd like to have the system figure out the last record
entered and detect the letters and know what should come next. That way if
the 28th record is entered (BB) it knows that the 29th record should be CC.
Is there an easy way to accomplish this using VBA? I could simply create a
table with the first 130 entries (A - ZZZZZ) but I'd rather have the system
work a bit more independantly (so in the event that there is 131 records I
don't run into trouble). Thanks!

 

[ArielZusya]

Hi Dave,

So... don't know how I missed this but for whatever reason I missed your
reply. sorry bout that. OK... so... I'm back now and I'll stop relying on
the "notify me of replies" and just be vigilant about checking back manually.

Rules... I agree with you completely about the logic but unfortunately the
folks I'm writing this for are less than logical. Funny thing is this is for
tracking exhibits in court. The way courts do this is for exhibits
introduced by either the plaintiff in civil matters or the prosecution in
criminal matters the courts will use 1, 2, 3, etc. but for defense they use
A, B, C ... Z, AA, BB, CC ... ZZ, AAA, BBB, CCC ... ZZZ, AAAA, BBBB, etc.
Unfortunately, while I'd like to say that no case is so complex that it will
have more than a certain number of exhibits, I can't with certainty so best
would be the ability for the system to keep going infinitely. That said, if
that's insane I'm happy to stop at 6 digits deep ZZZZZZ which would break the
150 mark for exhibits (I hope we don't see cases with more than that for
defense exhibits! The reason the prosecution/plaintiffs get the numbering is
because traditionally the prosecution has the majority exhibits. At any rate
a trial with ZZZZZZ exhibits would be a sleepy trial. *GRIN* Sorry...
couldn't resist.)

I'm pretty much self taught with vba and access and I've wanted to start
playing with loops (I'm assuming that's where we're headed) so I'm really
excited (and nervous). On the other hand if looping isn't necessary, I'll be
content with whatever I learn. Thanks again for all your help.

Ariel

I can show you how to write the code, but I need to know some rules.
How many characters can be in the string?
What is the order. For example, I would expect it would go from
A - Z
AA - AZ
AB - ABZ

In your original example, you show going form BB to CC, but that doesn't
seem logical. However, it is your app and your rules, so I need to know how
the order actually is.
--
Dave Hargis, Microsoft Access MVP

OK... so... that's so cool... quick question though... How do I get past 90?
91 seems to go to [ rather than to AA? I imagine that's because asc must be
returning the key mapped to that value or something like that, right? I
suppose I would have to have it do some math or something to get it to
concatonate the chr of two asc returns or something. I noticed that asc("AA")
returns 65 so I'm not clear on how to make this all work. Do I need to loop
this? Wouldn't it have been great if the two groups both used something
easier like GroupA1, GroupA2, GroupA3, etc. and GroupB1, GroupB2, GroupB3
etc. That I could do with no assistance. At least I'm learning something
new though. Any thoughts on what I should do next would be greatly
appreciated! Thanks for all your help.

I'm working on a dataset which numbers each entry by number (1, 2, 3, etc.)
for one group and by letter for another group (A, B, C, ... AA, BB, CC, ...
AAA, BBB, CCC, etc.). I'd like to have the system figure out the last record
entered and detect the letters and know what should come next. That way if
the 28th record is entered (BB) it knows that the 29th record should be CC.
Is there an easy way to accomplish this using VBA? I could simply create a
table with the first 130 entries (A - ZZZZZ) but I'd rather have the system
work a bit more independantly (so in the event that there is 131 records I
don't run into trouble). Thanks!