Unique fields using AAAA instead of/as well as AutoNum

[BenjieB]

Hi,

Does anyone know of a way of creating an auto generated/increasing in value
field (key) that uses chars instead of (or in addition to) Autonum e.g. GLAA,
GLAB, GLAC, GLAD...etc.?

Any ideas, views, comments or help would be very welcome.

Thanks

Ben

 

[Douglas J. Steele]

For what purpose? It's rare that you even show the value of an AutoNumber
field to the end user.

 

[BenjieB]

Hi Doug,

It's all very bizarre really, and originates from a legacy IBM system which
generated a unique client account ID of 4char. Not a problem initially but
it now appears that a 3rd party service provider requires the continuation of
the unique 4char Account ID as a sinon to their web support service - a
service which just happens to be a key part of the product being offerred.

For some reason the 4char Account ID were never generated in the range
GLAA-GLZZ. Therefore, an ideal opportunity to start new business account
ID's during the interim, until a replacement strategic web based solution is
implemented replacing the whole lot over the next 6 months (which of course
would be the time it takes the 3rd party to change it's login process and 5
months later than the commencement of issuing new business)

Hence trying to find some way around this, if you see what I mean.

Ben

 

[Douglas J. Steele]

I'd keep your standard AutoNumber field as the PK for the table, but add
another field where you generate the 4 character field.

Put a unique index on the field.

Write a function to generate the character key. Obviously the first two
characters will always be "GL". Generate a random number between 1 and 26
for the next two characters using the RND function.

 

[PC Datasheet]

AA to ZZ is 676 records (26*26). Keep your autonumber. Make the last
autonumber you have GLAA and create a function that calculates the four
characters based on the number of records entered since your last
autonumber. For example: say your last autonumber is 34511 and records have
been added and none deleted to where your current autonumber is 34536. 25
records have been added so your four characters are GLAZ. BTW, you can test
if there are any gaps in the numbers to determine if any records have been
deleted after 34511.

 

[BenjieB]

Nice one Doug,

Now to write the function...DOH!

Many thanks,

Ben

 

[BenjieB]

Hiya,

Thanks for this...greatly appreciated.

As it's for a high value low turnover product application with an extremely
optimistic estimate of 30 quotes max per month, 676 should be more than
sufficient for a 6 month interim solution. Also am none too sure of the
feasibility of this due to the user wants/needs to generate the 4char during
the quote stage; ineffect the quote ID becomes the acount ID upon sale as it
were.

Ben

 

[PC Datasheet]

That's OK! Just use the method with QuoteID when entering quotes to generate
the four character unique field. Orders are related to quotes so just carry
over the four character field when entering an order. Don't calculate the
four characters from OrderID.

 

[BenjieB]

Many thanks for your help on this one.

Ben

That's OK! Just use the method with QuoteID when entering quotes to generate
the four character unique field. Orders are related to quotes so just carry
over the four character field when entering an order. Don't calculate the
four characters from OrderID.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com