Field Prefixes/Preceeding Zeros

[lucour]

I have a database that tracks backup tapes. Each tape field is setup as a Text data type.
The external labels of the tapes we use are 'BK1'. What I had wanted to achieve in the Form design is that the user would just have to type in the tape number and the BK1 would display as a prefix. I was successful at this by setting up >"BK1" in the fields Format property. However, here is my predicament. Now, if the user enters '1', it will show as BK11. I need preceeding zero's to the left of the number depending on what number is entered. For example, if '1' is entered, it should show as BK10001; '10' should show as BK10010; and '110' should show as BK10110.
Basically, in a nutshell, if it is a 1 digit entry I need 3 preceeding zeros; 2 digit number will have 2 preceeding zeros; and a 3 digit number will have 1 preceeding zero.
Is this possible ?

Thanks!

 

[Jimbob]

duplicate

[This message has been edited by Jimbob (edited 05-21-2002).]

 

[Jimbob]

In access help look up the following functions:

Mid, InStr, Left, Right. You might be able to do something with these

OR, go to the help index and type 'String' and look under the sub heading 'Manipulation'

 

[Fizzio]

Set the format to "BK1"0000

 

[cogent1]

You can just use "BK" as a prefix. Then add 10000 to whatever code you enter. The field is the result of the calculation "BI" & (10000+codenumber). This automatically sets the right number of preceding zeroes.

 

[lucour]

This reply is for Fizzio:

If you use "BK1"0000 in the format property, this does not work. The next time I go to look at the format property, all of the 0's have moved within the quotations: "BK10000".

Thanks.