Long Integer truncates 0 at beginning of number

[Piper]

Hi -

I have a field of type Long, the data that goes in the field are numbers
like 027987 and 564982

Every time I enter a number with a 0 in front access takes the 0 out and
leaves me with 27987.

Is there anything I can do to keep the field type long? I've done a lot of
programming and I hate to loose it if I had to change the field type to
String.

Thanks

 

[Jeff Boyce]

Piper

Access is doing EXACTLY what you are telling it to do. Integers, Long or
otherwise, are whole numbers. From basic math, 1, 01, 001, and
00000000000000001 are all the same value, i.e,. 1!

If you need to show the digit 0 as the first character in a string of
characters (digits), then it is likely NOT a number (something you'd
add/subtract/multiply/divide). If you aren't doing math with it, maybe it's
"text", not "number".

Or, if you want to SEE preceding zeros on a true number (math, remember),
you can use the Format property or function to display those, regardless of
the value Access stores.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Piper]

Thanks for the response - I'm bummed, now I have to make it a string and
convert all that coding.

Oh well - I suppose I should have figured that out before I started coding!

Thanks
--
Piper

Piper

Access is doing EXACTLY what you are telling it to do. Integers, Long or
otherwise, are whole numbers. From basic math, 1, 01, 001, and
00000000000000001 are all the same value, i.e,. 1!

If you need to show the digit 0 as the first character in a string of
characters (digits), then it is likely NOT a number (something you'd
add/subtract/multiply/divide). If you aren't doing math with it, maybe it's
"text", not "number".

Or, if you want to SEE preceding zeros on a true number (math, remember),
you can use the Format property or function to display those, regardless of
the value Access stores.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

No, I don't think you need to redo all your coding. If the field in the
table where the value is stored is Numeric/Long, then anything that
references or modifies it as a number can be left as is.

The only time you need to present it as text is when you want to show it to
a human in that format. Then, you use the Format function or property as
Jeff said. That does not chage the original value or type unless you do so
specifically. For example, let's say you want to present it in a message box:

MsgBox "This Is A Formatted Number " & Format(lngSomeNumber, "000000")

Nothing has changed about the variable lngSomeNumber. It is just formated
for presentation so that it will always be presented with enought zeros to
make it six characters long.

If you want to change it to a string:
strConvertedNumber = Format(lngSomeNumber, "000000")

Now lngSomeNumber has still not changed, but we have a string variable that
contains a text representation of the number.


--
Dave Hargis, Microsoft Access MVP

Thanks for the response - I'm bummed, now I have to make it a string and
convert all that coding.

Oh well - I suppose I should have figured that out before I started coding!

Thanks

 

[Piper]

Let me see if I understand this correctly

I can leave the number as 27987 in the database, but if I need to show it,
say in a textbox, I can just format it like this:

Dim id as Long

id = 27987

sometextbox.value = Format(id, "000000")

Is that right?
If it is I'll be so excited!

 

[Klatuu]

Get excited, that is all there is to it.
The only difference is you will need to use the number of zeros necessary to
format it to the length you want. For example if it will alway be 6 long,
use 6 zeros, for 9 long 9 zeros. But, if you want 1 leading zero regardless
of how long the number is, use
sometextbox.value = "0" & Cstr(id)

Or, you can put the formatting in the format property of the text box
control and just use
sometextbox.value = id

 

[John W. Vinson]

Let me see if I understand this correctly

I can leave the number as 27987 in the database, but if I need to show it,
say in a textbox, I can just format it like this:

Dim id as Long

id = 27987

sometextbox.value = Format(id, "000000")

Is that right?
If it is I'll be so excited!

It's even easier than that: you need no code at all.

Just open the form or report in design view; select the textbox; and set its
Format property to "000000".

John W. Vinson [MVP]

 

[Piper]

AWESOME! Thanks for the help
--
Piper

Get excited, that is all there is to it.
The only difference is you will need to use the number of zeros necessary to
format it to the length you want. For example if it will alway be 6 long,
use 6 zeros, for 9 long 9 zeros. But, if you want 1 leading zero regardless
of how long the number is, use
sometextbox.value = "0" & Cstr(id)

Or, you can put the formatting in the format property of the text box
control and just use
sometextbox.value = id

 

[Piper]

It gets even easier?
Ha! This is great - its always better to know how to do things more than one
way just in case you need it.

Thanks a lot for the help