Trailing zero's

[Mike]

I thought maybe some here could shed some light on my problem. I have created
a vb form to imported an excel file which is created from quickbooks into an
access database.

I have to create a txt file from this data. My problem is the trailing
zero's of the Amount fields drop. So say if I have 19.00 I need to remove the
decimal and show it as 0001900. If I have 19.10 I need remove the decimal and
add the three zero's to show 0001910.

What is happen's is 19.00 ends up like 0000019 and 19.10 ends up like 0000191.

I know this dosen't really have anything to do with excel but thought
someone here might be able to help.

Thanks Mike

 

[Joel]

First, it is not cler where the problem is occuriung. You need to be more
spedific

1) I s the data correct in the text file?
2) Do the data get get put into the Excel form correctly?
3) Or does the data get corrupted when the data is transfered to Access.
give examples of the code where the problem is occuring.

Second, why would you want to multiply your numbrs by 100? Is the problem
occuring with evry numbers or only certain type of numbers?

Third, excel displays number differently then it stroes the number in
memory. A number has an option of displaying (fromating) to a different
number of decimal places. for example a number stored as 123.456789 and
formated to one decimal place will look like 123.4 but when a caculation is
made still uses the full number of decimal places.

Fourth, leading zeroes in Excel don't change the number in memory. It just
adds extra zeroes when the number is displayed. An excel format of #00000
will make every number appear as 5 digits preceeding number that are less
than 5 digits with leading zeroes. for example 123 will be display as 00123
while 12345 will not get any leading zeroes.

 

[ShaneDevenshire]

Hi,

I don't know from your question if this problem is occuring in Access, Excel
or the text file.

The basic solution is to multipley 19.00*100 to get 1900, but it also looks
like you need leading zeros so ="000"&19.00*100

Now I notice in one case you add 3 leading zeros in the other case 4, you
would need to tell us how you decide which to do.

Anyway the above formula can be entered in an Access Query and the results
of the query sent to Excel, or the formula can be applied in Excel after the
data is transfered.

In Acces it would be a new calculated field in the query and would read
something like
New Number:"000"&[Field Name]*100

This would be on the first row of the query, and Field Name would be the
name of the field containing the numbers.


If this helps, please click the Yes button.

 

[Mike]

Thank you
i was using the replace function to get rid of the decimal that must be
where i was losing the zero's

worked like charm

Hi,

I don't know from your question if this problem is occuring in Access, Excel
or the text file.

The basic solution is to multipley 19.00*100 to get 1900, but it also looks
like you need leading zeros so ="000"&19.00*100

Now I notice in one case you add 3 leading zeros in the other case 4, you
would need to tell us how you decide which to do.

Anyway the above formula can be entered in an Access Query and the results
of the query sent to Excel, or the formula can be applied in Excel after the
data is transfered.

In Acces it would be a new calculated field in the query and would read
something like
New Number:"000"&[Field Name]*100

This would be on the first row of the query, and Field Name would be the
name of the field containing the numbers.


If this helps, please click the Yes button.

--
Thanks,
Shane Devenshire

I thought maybe some here could shed some light on my problem. I have created
a vb form to imported an excel file which is created from quickbooks into an
access database.

I have to create a txt file from this data. My problem is the trailing
zero's of the Amount fields drop. So say if I have 19.00 I need to remove the
decimal and show it as 0001900. If I have 19.10 I need remove the decimal and
add the three zero's to show 0001910.

What is happen's is 19.00 ends up like 0000019 and 19.10 ends up like 0000191.

I know this dosen't really have anything to do with excel but thought
someone here might be able to help.

Thanks Mike

 

[Dave Peterson]

=text(a1*100,"0000000")
or
=text(a1*100,rept("0",7))