leading zero's were lost

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

Guest

I have a problem where I cannot control the format of the
data I recieve. Some of the data comes to me with the
leading zero's dropped. i.e. intead of 000352 I get 352
or 007892 comes as 7892. The # of characters varies, 7 is
the max it should have to as few as 1 may be reported .
they all should be 9 characters to work correctly for
what I am doing. How can I restore the leading zeros when
the # of charcters varies from recored to record?

123 should be 0000123
3521 should be 0003521
69871 should be 0069871
 
I have a problem where I cannot control the format of the
data I recieve. Some of the data comes to me with the
leading zero's dropped. i.e. intead of 000352 I get 352
or 007892 comes as 7892. The # of characters varies, 7 is
the max it should have to as few as 1 may be reported .
they all should be 9 characters to work correctly for
what I am doing. How can I restore the leading zeros when
the # of charcters varies from recored to record?

123 should be 0000123
3521 should be 0003521
69871 should be 0069871
Click to expand...

Are you using a numeric field DataType or a character DataType? Numeric
fields hold numeric values (which means no leading zeros). If you want to
retain leading zeros you must use a character (text) field.

If you just want to *see* leading zeros you can use a numeric field and
formatting.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT Format(number_col, String(9,"0")) As StringNumber
FROM ...

This will change the data type of the number to a string data type.
Is this what you want?


MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQD+5L4echKqOuFEgEQIcZQCdFDGeKPqTdPs25jD9kEorK6PXjQcAoK/q
AsLIUE9hbFTsYUHLzZlWntP4
=DvSd
-----END PGP SIGNATURE-----
 
Define the field that receive the "0000123" data as Text.
Currently it is being seen as and stored as a number -
i.e. you don't need leading zeros on real numbers so it
chucks them. Remember what you want the computer to see
and it will interpret the data accordingling. See the
data properties in the table and queries. you can get
some very interesting and completely unexpected results it
you don't manage the movement of data between different
types of data, i.e. character, logical, integer short,
integer long, decimal, real short, real double, etc...
your problem occured becase the computer converted text to
integer long when in fact you wanted text to text.

Hunter
 
Define the field that receives the "0000123" data as
Text. Currently it is being seen as and stored as a
number - i.e. you don't need leading zeros on real
numbers so it chucks them. Remember what you want the
computer to see and it will interpret the data
accordingling. See the data properties in the table and
queries. you can get some very interesting and completely
unexpected results it you don't manage the movement of
data between different types of data, i.e. character,
logical, integer short, integer long, decimal, real short,
real double, etc... your problem occured becase the
computer converted text to integer long when in fact you
wanted text to text.

Hunter
 
Back
Top