Format autonumber

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an autonumber such as 50001, 50002, 50006 and would like to have it
displays on the form or report or query in the format with 1 leading zero:
050001, 050002, 050006...

Please help. Thanks much. (This newsgroup is a great help for me)
 
Hi, Andrew.

Convert it to a string and concatenate it with a "0" character. If it's
always five characters, not beginning with zero, then a ControlSource of this
should do it:

= "0" & Str([YourAutoNumberField])

If it could be of variable length, you could use nested IIf statements,
checking the various cases of length, but it's cleaner to write a custom
function, which you can put in the form module you use it in, or in a
standalong module, if you might reuse it in another application. In the
latter case, declare the function Public:

Public Function FillLeadingZero(intValue As Integer) As String
Const intDesiredLength = 6
Dim inti As Integer, intCurLength As Integer

FillLeadingZero = Str(intValue)
intCurLength = len(FillLeadingZero)
For inti = 1 to (intDesiredLength - intCurLength)
FillLeadingZero = "0" & FillLeadingZero
Next inti
End Function

Then set your ControlSource to a call to the function, passing it the
AutoNumber field:

=FillLeadingZero([MyAutoNumberField])

To make the function more general, you could also pass the total string
length as a second parameter rather than making it a constant.

Hope that helps.
Sprinks
 
Hi Sprinks,

I tried = "0" & Str([YourAutoNumberField])
at the control source but it doesn't work.
It said the express either incorrectly type or too complicated to evaluate.

Sprinks said:
Hi, Andrew.

Convert it to a string and concatenate it with a "0" character. If it's
always five characters, not beginning with zero, then a ControlSource of this
should do it:

= "0" & Str([YourAutoNumberField])

If it could be of variable length, you could use nested IIf statements,
checking the various cases of length, but it's cleaner to write a custom
function, which you can put in the form module you use it in, or in a
standalong module, if you might reuse it in another application. In the
latter case, declare the function Public:

Public Function FillLeadingZero(intValue As Integer) As String
Const intDesiredLength = 6
Dim inti As Integer, intCurLength As Integer

FillLeadingZero = Str(intValue)
intCurLength = len(FillLeadingZero)
For inti = 1 to (intDesiredLength - intCurLength)
FillLeadingZero = "0" & FillLeadingZero
Next inti
End Function

Then set your ControlSource to a call to the function, passing it the
AutoNumber field:

=FillLeadingZero([MyAutoNumberField])

To make the function more general, you could also pass the total string
length as a second parameter rather than making it a constant.

Hope that helps.
Sprinks



Andrew said:
I have an autonumber such as 50001, 50002, 50006 and would like to have it
displays on the form or report or query in the format with 1 leading zero:
050001, 050002, 050006...

Please help. Thanks much. (This newsgroup is a great help for me)
Click to expand...
Click to expand...
 
Hi, Andrew.

What is the name of your AutoNumber field? Did you use this in the
expression? Cut, paste, and repost the expression from the Control Source
exactly.

BTW, once we get it working, I left out from my first post that you need to
use the LTrim function to avoid leading spaces:

"0" & LTrim(Str([YourAutoNumberField]))


Andrew said:
Hi Sprinks,

I tried = "0" & Str([YourAutoNumberField])
at the control source but it doesn't work.
It said the express either incorrectly type or too complicated to evaluate.

Sprinks said:
Hi, Andrew.

Convert it to a string and concatenate it with a "0" character. If it's
always five characters, not beginning with zero, then a ControlSource of this
should do it:

= "0" & Str([YourAutoNumberField])

If it could be of variable length, you could use nested IIf statements,
checking the various cases of length, but it's cleaner to write a custom
function, which you can put in the form module you use it in, or in a
standalong module, if you might reuse it in another application. In the
latter case, declare the function Public:

Public Function FillLeadingZero(intValue As Integer) As String
Const intDesiredLength = 6
Dim inti As Integer, intCurLength As Integer

FillLeadingZero = Str(intValue)
intCurLength = len(FillLeadingZero)
For inti = 1 to (intDesiredLength - intCurLength)
FillLeadingZero = "0" & FillLeadingZero
Next inti
End Function

Then set your ControlSource to a call to the function, passing it the
AutoNumber field:

=FillLeadingZero([MyAutoNumberField])

To make the function more general, you could also pass the total string
length as a second parameter rather than making it a constant.

Hope that helps.
Sprinks



Andrew said:
I have an autonumber such as 50001, 50002, 50006 and would like to have it
displays on the form or report or query in the format with 1 leading zero:
050001, 050002, 050006...

Please help. Thanks much. (This newsgroup is a great help for me)
Click to expand...
Click to expand...
Click to expand...
 
Have a look at the help on masks.

I have an autonumber such as 50001, 50002, 50006 and would like to have it
displays on the form or report or query in the format with 1 leading zero:
050001, 050002, 050006...

Please help. Thanks much. (This newsgroup is a great help for me)
Click to expand...

Please remove obvious from email address if emailing.
 
Hi all,

I did check on Help and it took me a while...finally it works! Thanks all
for helping.
1) My field name is "ID".
2) I use custom format '000000' to displayed 50001, 50002, 50006 as 050001,
050002, 050006.

Again, thank you so much. What a community we have.
 
Back
Top