Automatically create a UNIQUE # (autonumbering w/characters)

[Guest]

Hi Everyone!

I have a table and I need to input an Item No. for each Part No. given.

*Each Item No. has to be unique (sort of like autonumber, primary key)
*Each Item No. begins with CRI
*Each Item No. ends with 000

Here is where the tricky part (at least for me) comes in. There needs to be
4 characters between the CRI and the 000...example CRIAERD000...NO SYMBOLS
THOUGH. It can be any 4 characters, it just has to be unique.

Is there a way I can have Access create those 4 characters for me? Please
help!!!

Thanks

 

[Jeff Boyce]

Crystal

Any reason you couldn't use an autonumber for the part in the middle?
They're unique (but digits, not alphabetic characters).

If you need alphabetic characters, could you generate a random number for
each charcter, then use the CHR() function to return an alphabetic character
for the random number? You'd have to limit the range of random numbers to
those for which CHR() generates alpha characters.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff -

Maybe I should have posted my question in the "New Users" section...LOL

I don't see why I couldn't just use numbers, it would be easier, however we
currently have Item #'s setup with alpha & numeric.

As far as your suggestion below...sounds reasonable...but I'm still pretty
new and you've stumped me on the CHR( ). Could you show me what the code
would look like?

Thanks!

 

[Jeff Boyce]

Crystal

In a query (or in a procedure you'd write), you'd use the RND() function and
the CHR() function to 1) generate a random number; 2) get an ASCII
character based on that random number.

Access HELP and VBA HELP can give you both the correct syntax and examples
for each.

The one thing you'd need to do is make sure that the range of RND() numbers
falls in the range of numbers that will produce usable characters. For
example, if your RND() returns "10", the CHR() function will return a
carriage return (or is it a line-feed, I can never remember which is which).
This is NOT an alphanumeric character that you'd be able to use.

And now that I think about it, since you'd be using a random number
function, there's no guarantee that you wouldn't end up with a duplicate!
Maybe you better reconsider using the Autonumber for this portion...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

THANKS JEFF
: )

Crystal

In a query (or in a procedure you'd write), you'd use the RND() function and
the CHR() function to 1) generate a random number; 2) get an ASCII
character based on that random number.

Access HELP and VBA HELP can give you both the correct syntax and examples
for each.

The one thing you'd need to do is make sure that the range of RND() numbers
falls in the range of numbers that will produce usable characters. For
example, if your RND() returns "10", the CHR() function will return a
carriage return (or is it a line-feed, I can never remember which is which).
This is NOT an alphanumeric character that you'd be able to use.

And now that I think about it, since you'd be using a random number
function, there's no guarantee that you wouldn't end up with a duplicate!
Maybe you better reconsider using the Autonumber for this portion...

Regards

Jeff Boyce
Microsoft Office/Access MVP