Alpha Numeric sequences

[Guest]

I have a database where we manage our job records, Each of our outlets has a
numbering system that starts with a letter and then a number eg A1234, B1234.
I would like to keep the jobs together in one table. Is there a way of
looping through the Job no field and choosing the last No of a series and
incrementing it. I know how to increment numerically but have never needed to
do this with a letter in the code. I presume there would be some way of
stripping off the 1st character, converting the data to number, incrementing
the number and then putting back the character. Does this sound the right way
to tackle this. It is an Access 2000 database. Grateful for any help

 

[Alex Dybenko]

Hi,
code looks like:

?"B" & val(mid("B1234",2))+1

perhaps you can write a function which makes increment

Function Increment(var)
If Len(var & "") > 0 Then
Increment = Left(var, 1) & Val(Mid(var, 2)) + 1
End If
End Function

 

[John Spencer]

One minor change, suggestion. If your numbers had leading zeroes - B0024 -
then you will need to apply some formatting in the code to force the leading
zeroes.

Increment = Left(var, 1) & Format(Val(Mid(var, 2)) + 1,"0000")

Also, if you do have the formatting, you will run into an error when the
"Number" reaches 10,000.

A good idea would be to split you current field into two fields. One for
the number part and one for the letter part. That would make things easier.
When you need the entire thing, you can simply combine the components.

 

[Guest]

Thanks for the help
If the code was split into 2 fields how would I be able to increment each
outlet by 1 - would I not end up with non sequential numbers for each outlet
- we like the sequential to ensure we have not missed any orders - is there a
way around this

 

[John Spencer]

You would specify the Outlet when looking up the JobNumber for that Outlet.

Nz(DMax("JobNo","YourTable","Outlet='" & "A" & "'"),0) + 1

 

[Tim Ferguson]

I have a database where we manage our job records, Each of our outlets
has a numbering system that starts with a letter and then a number eg
A1234, B1234. I would like to keep the jobs together in one table. Is
there a way of looping through the Job no field and choosing the last
No of a series and incrementing it.

Your job would be MUCH easier by getting the design right. You need to
split your SeriesLetter and JobNumber into separate fields. You can
concatenate them on a form or a report so they look normal to your users;
meanwhile your jobs of finding new numbers and grouping on SeriesLetters
become trivial.

You might like to read up on First Normal Form...


Best wishes


Tim F