4 character numbers

[Edgarr8]

Hello,

I have an autonumber i would like to store as a 4 character number

For example
Autonumber [ID] 1 make it 0001

I tried changing the format to "0000" but when i do that and then try to use the Autonumber column i lose all my 000

for example if i want to add 084567 to that id i would use
084567 & [ID] and my desired outcome would be 0845670001
but i get 0845671..

How can i get it to store as a 4 character number? ex. 0001?

 

[wazz]

are you trying to store this in a new field, or trying to alter the autonumber field data?

edit: nevermind, i see what you're doing.

 

[wazz]

try:

    Dim intZeros As Integer

    With Me
        intZeros = 4 - Len(.IDField)
        .NewID = "084567" & String(intZeros, "0") & .IDField
    End With

if you put a new field in your table called NewID and put the code above in the On Current event of a form, the NewID will be updated to the value you want when you move to a record. i'm sure you'll want to do this another way but maybe that can get you going.

 

[Edgarr8]

Thank you that does give me an idea where to go...

 

[Edgarr8]

You open like 1000 doors for me with that, like something cliked..haha

this is what i did to be able to get it in the query without having to go into the code stuff

NewID: [Company Prefix] & String((4-Len([ID])),"0") & [ID]

Thank you!

 

[wazz]

great! as long as you don't have a 5-digit ID you're good to go.
thanks for the update.