Generate Alphanumeric number

[wisey]

I have inherited an old dbase 5 database. It's a database for filing photo negatives and so it generates alphanumeric numbers for the the start and end of the negatives and also an alphanumeric packet number. The format for the negatives is as follows eg:-

AV000-AV999
AW000-AW999
AX000-AX999
AY000-AY999
AZ000-AZ999
BA000-BA999
BB000-BB999
and so on.

I would like to import this data into access 2000 and have a VB module generate the next alpha number in the sequence looking at the last records end negative number as the source. This is becuase they key in the end number which might be any number of numbers after the start negative number you generate. This is hard to explain. eg.
we generate first neg number = AT123.
they would say they have 5 negs in this group so they
would key in the End neg number = AT128.
the next number we need to generate programmatically would be AT129. Please can someone help!!!!

 

[MichaelRed]

Wisey,

This can be done a number of ways, the choice(s) probably depending on some additional information.

Do you always know the number of items you need to add when you start to add the records?

Do you always know the starting number when you start to add the records?

Do you always know the alpha portion of the record id when you start to add the records?

If the answers to all of the above are "YES", then the process is simply to build the string from the alpha and the trim(string(NumberPart) where "NumberPart" starts with the starting value and continues (incrementing) for the number of records to be added minus 1.

Assume the AlphaPart is known to be "AT", the StrtNum ("NumberPart&quot is known to be 123, and NumRecs (the number of records) is known to be 5 (your example):

For Idx = 1 to 5
[tab]tmpNum = StrtNum + Idx - 1
[tab]NegId = AlphaPart & Trim(Str(StrtNum))
[tab]'Do the rest of the record processing here
Next Idx

This is some what 'quick and dirty', but should give you a startign point.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Wisey,

This can be done a number of ways, the choice(s) probably depending on some additional information.

Do you always know the number of items you need to add when you start to add the records?

Do you always know the starting number when you start to add the records?

Do you always know the alpha portion of the record id when you start to add the records?

If the answers to all of the above are "YES", then the process is simply to build the string from the alpha and the trim(string(NumberPart) where "NumberPart" starts with the starting value and continues (incrementing) for the number of records to be added minus 1.

Assume the AlphaPart is known to be "AT", the StrtNum ("NumberPart&quot is known to be 123, and NumRecs (the number of records) is known to be 5 (your example):

For Idx = 1 to 5
[tab]tmpNum = StrtNum + Idx - 1
[tab]NegId = AlphaPart & Trim(Str(StrtNum))
[tab]'Do the rest of the record processing here
Next Idx

This is some what 'quick and dirty', but should give you a starting point.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[wisey]

Thanx Michael.

I ended up doing it this way after trial and error:-

Dim currentdbin As Database
Set currentdbin = currentdb()
'Dim tblname As Recordset
Set tblname = currentdbin.OpenRecordset("findnegnum&quot
Dim inputnum As Variant
Dim changenum As Boolean
Dim chfirst As Boolean
Dim chsec As Boolean
changenum = False
chfirst = False
chsec = False
tblname.MoveLast
inputnum = tblname!NEG_NUM_2
'inputnum = "AT1234"
lenstr = Len(inputnum)
If lenstr = 3 Then l = 1
If lenstr = 4 Then l = 2
If lenstr = 5 Then l = 3
If lenstr = 6 Then l = 4
firstbyte = Asc(Left$(inputnum, 1))
secbyte = Asc(Mid$(inputnum, 2, 1))
thirdbyte = Right$(inputnum, l)

'now test for number change
If thirdbyte < 9999 Then changenum = True
If thirdbyte >= 9999 Then chsec = True
If secbyte >= 90 And thirdbyte >= 9999 Then chfirst = True

Do While changenum = True
thirdbyte = thirdbyte + 1
changenum = False
Loop

Do While chsec = True
thirdbyte = 0
secbyte = secbyte + 1
chsec = False
Loop

Do While chfirst = True
thirdbyte = 0
secbyte = 65
firstbyte = firstbyte + 1
chfirst = False
Loop
numout = Chr$(firstbyte) & Chr$(secbyte) & thirdbyte
Me![txtNEG_NUM_1] = numout

A bit messy, but it will take the last neg_number in the format 'AA0000' (two alpha's,4 numbers) and tell you the next one in the sequence reliabley. Thanks again for your help.

jason