Number Data Type Field and Leading Zeros

[Carlos]

I have a table that has one field set to a number data
type. As part of the values input process, I need to enter
values leading with a zero as the first digit.
Unfortunately, every time we try to enter one of these
values, Access drops the leading zero automatically. Also,
I can't change the data type to text since programming
code in other applications tied in to this table require
the field to be a number data type.

Any suggestions on how to keep the leading zero as part of
the values without having to change the data type?

Thank you.

 

[Guest]

Any suggestions on how to keep the leading zero as part of

the values without having to change the data type?

Hi Carlos,

Leading zeros have no meaning with numeric values: 1 = 01 = 0001, etc. If the data must be numeric but you need the humans to work with a certain format, you must convert to String for display.

Try something like this, using 8 as an example:

Dim str as String
Dim int As Integer

int = 123
str = CStr(int)

Do While (Len(str) < 8)
str = "0" & str
Loop

''' str = "00000123"
''' CInt(str) = 123

Jay

 

[Ken Snell]

Can't be done with a number formatted field. You will have to change the
field to text format.

Text can easily be converted to numbers via Val function and other means.
Your other programs will need to do the conversion.

 

[John Nurick]

Hi Carlos,

As others have said, a leading zero cannot form part of a numeric value:
12345 is the same *number* as 012345, though not the same string of
characters. But you can *display* leading zeroes by setting the Format
property of the control or field to something like
00000

 

[Dennis Snelgrove]

Or by using "Right$("00000000" & [ValueField],8)

Hi Carlos,

As others have said, a leading zero cannot form part of a numeric value:
12345 is the same *number* as 012345, though not the same string of
characters. But you can *display* leading zeroes by setting the Format
property of the control or field to something like
00000