Account Number creation

[Ploho]

I am creating a table to hold account details, i wondered if it was possible to force the ID column (Autonumber) to have 5 digits (i.e. 00001, 00011, 00111 etc.).

I have tried using the format option, however have found that this only displays it as a five digit number and only uses a one digit number.

If it is possible, please give it in simple easy to follow steps as i don't really know what i am doing that well.

Thank you.

 

[Scooterbug]

The autonumber should only be used to generate an unique number. As far as I know, you cannot force it into a certain format. If you are looking to assign a custom number (and it would have to be a text field because of the leading zeros), you will have to use code to generate it.

Edit:
You can force an autonumber to start at a set number. You could use an append query to append the start number into the autonumber field.

 

[boblarson]

Autonumbers should really be for the use of the system. They guarantee to generate a unique number. They are not guaranteed to always be incremental or fall in a certain range. They also can have gaps, for example, when someone starts a record and then cancels it, or when a record is deleted.

So, if you want numbers to always act a certain way, it is best for you to handle them. You can do so by using a function (DMax) and adding 1 to it. I would also suggest that you do not worry about what the number is stored as (the zeros) but instead FORMAT it when you want to look at it. Otherwise you will need to use TEXT for the number and that can bring other problems.

 

[gemma-the-husky]

but in principle if you want a 5 character account code, then you need to structure the field as text, not as a number, so you actually store the string "00011" etc.

personally, I would be inclined to have text account codes (and have an autonumber PK AS WELL) so I could change the account code, if necessary.

 

[boblarson]

but in principle if you want a 5 character account code, then you need to structure the field as text, not as a number, so you actually store the string "00011" etc.

personally, I would be inclined to have text account codes (and have an autonumber PK AS WELL) so I could change the account code, if necessary.

 

[Ploho]

The autonumber should only be used to generate an unique number. As far as I know, you cannot force it into a certain format. If you are looking to assign a custom number (and it would have to be a text field because of the leading zeros), you will have to use code to generate it.

Edit:
You can force an autonumber to start at a set number. You could use an append query to append the start number into the autonumber field.

Does this therefore mean that:
- i could actually force the autonumber to start at 00001 if i wanted to?

Also what would be the coding for the custom number in a text field?

 

[Scooterbug]

I think you are misunderstanding Autonumbers should ONLY be used by the system to mark a record as unique. If it's a number that you need to use, Dont use an autonumber. Autonumbers can skip around for starters, so relying on them to be totally sequential will disappoint you

Second, since the account numbers you are looking for will have leading Zeros, it cannot be a number. You will have to use a text field.

As far as getting your account numbers to follow the format you want, you can do one of two things.

1. Use dmax() function to find the last number assigned to account number. From here, you have two paths:
A. Use a Select Case to determine how many zeros to add to the dmax number. Something like this:

dim NextNumber as long
 
nextNumber = dmax("[AccountNumber]","TableA") + 1
 
Select Case len(nextNumber)
  Case < 10
     me.AccountNumber = "0000" & nextNumber
  Case > 10 and Case >100
     me.AccountNumber = "000" & nextNumber
Etc.....Add other cases.....
 
End Select

Or you can store the number without the zeros...and create a function similar to the Select Case above..then when you need to actually display the Account Number with the code, you can use the function to return the Account number with the correct amout of zeros

 

[Ploho]

I think you are misunderstanding Autonumbers should ONLY be used by the system to mark a record as unique. If it's a number that you need to use, Dont use an autonumber. Autonumbers can skip around for starters, so relying on them to be totally sequential will disappoint you

Second, since the account numbers you are looking for will have leading Zeros, it cannot be a number. You will have to use a text field.

As far as getting your account numbers to follow the format you want, you can do one of two things.

1. Use dmax() function to find the last number assigned to account number. From here, you have two paths:
A. Use a Select Case to determine how many zeros to add to the dmax number. Something like this:

dim NextNumber as long
 
nextNumber = dmax("[AccountNumber]","TableA") + 1
 
Select Case len(nextNumber)
  Case < 10
     me.AccountNumber = "0000" & nextNumber
  Case > 10 and Case >100
     me.AccountNumber = "000" & nextNumber
Etc.....Add other cases.....
 
End Select

Or you can store the number without the zeros...and create a function similar to the Select Case above..then when you need to actually display the Account Number with the code, you can use the function to return the Account number with the correct amout of zeros

Hi,

Thanks for the coding, does this go into a form (sorry - but i'm quite new to this thing) - if so, where abouts?

 

[Scooterbug]

You have to decide when you want the Account Number generated. You can either have a button that when pressed will generate the Account Number. Or you can have the Account Number generated right before the record is saved. Either way, the code would be on the Event that you choose.

 

[neileg]

Mmm...

I agree 100% about not using an autonumber for this. However, you can easilly use formatting to DISPLAY a number with 5 digits, even if the stored value has less. I prefer to do my formatting at the last minute, in a form or report and to store the number in its natural state.