leading zero value help

[Cam]

Hello,

I have two tables with a text field and the value in the field range from 50
to 9999.
50 is 2 characters, 100 is 3 characters and 9999 is 4 characters. I ran into
a problem when referencing these values in calculation.

How or what can I do to put two leading zero to 2 character field and one
zero to 3 character field on all existing records? (Maybe a macro, I don't
know) There are currently over 2000 records so it is sufficent to manually
change them all. Any help is appreciated.

Example:
50 = 0050
200 = 0200
950 = 0950

 

[KARL DEWEY]

I ran into a problem when referencing these values in calculation.
Your calculation must not be math as arithmetic numbers do not have leading
zeros.
If the field only contains numerials the use this ---
Format([YourField],"0000")
If it has numbers and alpha characters then use ---
Right("000" & [YourField], 4)

 

[fredg]

Hello,

I have two tables with a text field and the value in the field range from 50
to 9999.
50 is 2 characters, 100 is 3 characters and 9999 is 4 characters. I ran into
a problem when referencing these values in calculation.

How or what can I do to put two leading zero to 2 character field and one
zero to 3 character field on all existing records? (Maybe a macro, I don't
know) There are currently over 2000 records so it is sufficent to manually
change them all. Any help is appreciated.

Example:
50 = 0050
200 = 0200
950 = 0950

= Format(TextField],"0000")
will DISPLAY all of the values as TEXT with 4 digits.
So, you could run an update query to change all of the data in your
table to a 4 digit number, using the above expression .....

However, if you are performing math with the data, the field should be
a number field, not text. You cannot do math directly with a text
field, i.e. text strings of "0050" + "25" = "005025".

While you could use Val((FieldName]) to use the text value as a number
value, even then 50 and 0050 are equivalent.
Val("0050") + Val("25") = 75.

Change the field to a Number datatype.