Leading zeros

broadbean

Registered User.
Local time
Today, 16:55
Joined
Oct 24, 2007
Messages
15
I have an alphanumeric primary key that goes

REF0001
REF0002
REF0003

e.t.c.

When i get to REF9999 and enter REF10000 it does not store it after
REF9999 but stores it after REF100. Why is this ?
Is there a quick way that i can add leading 0's onto my records
so it will read

REF000001
REF000002
REF000003

e.t.c
 
"REF" & Format([somenumber],"000000")

However if all your codes start with REF you should simply store the number in the table and format it for display with the following Format property of the textbox on forms and reports.

"REF"000000

BTW Long strings are not the best as Primary Keys but the simple number would be good.
 
the reason it sorts "wrongly" is because it is doing a dictionary sort

so REF1nnnn always precedes REF9nnnnnnn

REF100
REF10000
..
..
REF2
..
REF999

If you go this way, you need a forced number that is long enough to accommodate the maximum size you will get to - say 6 numbers. Better still store the two bits separately - the prefix and the actual number.

And as G suggested, if the prefix is always REF, then you do not need to store the prefix anyway - although changing that now might be awkward.
 
Thanks for the posts guys.

I have been looking into this a bit. Problem was i had REF0001,REF0002,REF0003 e.t.c. Then someone typed in REF999 instead of REF0999.
I didn't know about this problem until i entered REF10000 and thought i had lost the record but as you say it puts it after REF100.
As i have a lot of records obviously i don't want to change each one individually as would take forever.
I came across a function called RemoveAlphas which strips away the text then you can use format(fieldname,"000000").
However like you said will be easier if i have a text field called "REF" and then have an autonumber as the other field. I found people typed in the wrong number when entering REF so instead of say REF0542 somone put in REF0842 by mistake. Then next person would come along, not realise the mistake and just follow on with the next number REF0843 instead of REF0543.
I know the autonumber solution will not fix this but i see no other easy way.
This was my primary key field REF so each individual record is unique but obviously does not follow order due to peope's typing errors.
Looking back wish i had used autonumber but hindsight is a wonderful thing.
I guess there is no way to autoincrement an alphanumeric primary key
 
The thing about an autonumber is that you might get gaps. An autonumber will not guarantee an intact sequence. if you want a sequence, then either take the next number from a control table - or do a dmax to find the highest current value, and increment it by one.

matter of taste which you prefer.
 
As I said earlier, if all the values start with REF then it should not be stored but only displayed. Similarly the leading zeros.

As it stands it isn't a good choice as a key field anyway.

Autonumber is not a good option for a displayed value.

Neither is haiving user enter a number as a sequence.Deach the forum for DMax + 1
 

Users who are viewing this thread

Back
Top Bottom