unique field

[teel73]

Is there a way to format the random auto number to just be 4 digits long? Or does anyone know how I can programatically create an auto unique field? This number has to be stamped on a key so it can't be longer than 4 characters but it has to be unique. Any help would be greatly appreciated.

 

[georgedwilkinson]

You scare me. It sounds like you're trying to create a smart key...always a bad idea. Just ask all the corporations who paid billions in 1999 to fix the smart key problem commonly referred to as "the Y2K bug."

Other than that, I'm pretty sure I saw this question answered at least once today and at least 5 times in the last week.

Here is the most recent (I think):
http://www.access-programmers.co.uk/forums/showthread.php?t=146404

That should get you on the right track. It won't solve the issue of making the auto-number stop at "9999", though.

 

[ColinEssex]

The auto-number is purely an internal number to link data between tables. It is not advisable to use it for anything else - like invoice numbers, order numbers etc etc.

Col

 

[teel73]

Thank you but none of that helped. I don't need to anything with dates. I just simply want a number or number combined with letters that will be unique for each record I add. The unique identifier can't be more than 6 characters because the key isn't that big for more than 6 characters to be printed on it.

You scare me. It sounds like you're trying to create a smart key...always a bad idea. Just ask all the corporations who paid billions in 1999 to fix the smart key problem commonly referred to as "the Y2K bug."

Other than that, I'm pretty sure I saw this question answered at least once today and at least 5 times in the last week.

Here is the most recent (I think):
http://www.access-programmers.co.uk/forums/showthread.php?t=146404

That should get you on the right track. It won't solve the issue of making the auto-number stop at "9999", though.

 

[georgedwilkinson]

Ok, I'll bow out of this thread, then.

Good luck!

 

[Rabbie]

If you are sure that you will never need more than 9999 keys just start with 0001 and use the MAX function to find the current largest key being used and add 1 to it to generate the next key.

The link in George's post will give you the right idea. Just omit the date information.

Just to clarify in your first post you say a max of 4 chars but later on you say 6 chars. Which is it.

 

[teel73]

I'm sorry. It will be a max of 6 characters, but I may inventory over 999,999 keys. I want a random number of 6 characters. Can I only get 999,999 different combinations with 6 characters?

If you are sure that you will never need more than 9999 keys just start with 0001 and use the MAX function to find the current largest key being used and add 1 to it to generate the next key.

The link in George's post will give you the right idea. Just omit the date information.

Just to clarify in your first post you say a max of 4 chars but later on you say 6 chars. Which is it.

 

[Rabbie]

I'm sorry. It will be a max of 6 characters, but I may inventory over 999,999 keys. I want a random number of 6 characters. Can I only get 999,999 different combinations with 6 characters?

IF you allow letters as wells as digits then you can have many more combinations. 999,999 is the max combinations using digits only.

 

[gemma-the-husky]

you can get a random 4 figure number by

int(rnd(x)*10000) i would think

but there is no guarnatee its unique

---------
why not use a counter field in a table ,and increment it from 0001 to 9999. then start again