Zero Fill a Field and remove decimal

[ms]

Access 2000:
I have numeric fields in a table with decimal and non-decimal values. I need to
zero-fill these fields and remove the decimal but keep the 2 digits to the
right of the decimal.
For example, I have records with values such as 23.45 and 1000 from a Text field
and am inserting them into a Text field with 13 places which must be
zero-filled. So they look like this: 0000000002345 and 0000000001000
respectively. I can change the datatype also if needed.

These records were inserted, using a JOIN query, from linked tables. If there is
a way to zero-fill upon insert vs modifying existing records in the table I am
open to either option.

Thanks for the advice.

 

[Tom Ellison]

Dear ms:

You cannot zero fill a numeric "field". You can, however, arrange to
display it zero filled. You can also multiply it by 100 when you want
to display it. Would that be a worthwhile option? Do you want users
to have to enter all the leading zeros and move the decimal point
whenever they are inserting or updating data? What is the reason why
you want the values stored this way, as opposed to just displaying
them this way?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[ms]

Hi Tom,
I am migrating data from one db to another. The table on the old db is linked
in MAS90. I am simply running a query to populate a new table in Access. This
new access table is then used to create a fixed width text file which is then
inserted into a new db.
I am not concerned with the Display of the record. I need these records to be
zero filled to import into the new system. So I need the actual field to be
zero filled or the export file.
Thank you,
mark

 

[Tom Ellison]

Multiply by 100 to get rid of the decimal point. Append a sufficient
number of zeroes to the left side of the number. Then take the
rightmost N characters of this string and you should have what you
want.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[ms]

Thank you Tom.
How would you suggest appending the appropriate number of zeroes given the
rightmost N chars. will vary (ex. 23.45, 235, 1000, etc)?
In the Insert statement?
ms

 

[Tom Ellison]

Dear ms:

That should work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[ms]

Tom,
How?

Dear ms:

That should work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear ms:

How do you do this in an insert query? For that column:

= RIGHT("000000000000" & CStr([NumericColumnName] * 100), 13)

In your initial examples, the value 23.45 is to come out
0000000002345, but the value 1000 is to come out 0000000001000.

How would the value 10.00 appear? Wouldn't that be 0000000001000? So
ten and one-thousand are the same? Or does 1000 become 0000000100000?
Anyway, that's why I expected, an implied decimal point.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Spencer (MVP)]

Well, first you have to multiply by 100 and then truncate.

Format(Int(YourNumber*100),"0000000000000")

If you are handling the number 23.45 it becomes 2345 with leading zeroes.
If you are handling 1000 it becomes 100000 with leading zeroes.

If you don't do this then you get confusion is 0000000001000
1000 or 10.00?

The last two digits are the tenths and hundredths of the number in all cases.

 

[ms]

Thanks Tom and John. That helped. I will treat everything as if it has a
decimal point and apply the multiplier to each record.
mark