Add decimal places

[sunilvedula]

Hi All,

I am importing excel sheet data into a table. It has a field for amt and it imports into temp table as text and then sends it to main table where it converts it into currency. Now the problem is the data from the excel sheet looks like 00002145 and when it comes to the main table it makes it $2,145. I want it to be 214.5. We can for sure assume the last two digits to be after teh decimal.i.e. units.

 

[namliam]

Now the problem is the data from the excel sheet looks like 00002145 and when it comes to the main table it makes it $2,145. I want it to be 214.5. We can for sure assume the last two digits to be after teh decimal.i.e. units.

In your sample though only 1 digit??

simply / 10 or / 100 as necesary, I assume your actually converting the string using CDbl or CInt?? CDbl([yourtextfield])/100

 

[sunilvedula]

no i am not converting just dumping the data. so u tell me that CDbland dividing it by 100 should help. am i right?

 

[sunilvedula]

Yes got it. sorry my mind did not work enough. thanks a lot Cdbl([myfield]/100) worked

 

[namliam]

Yes got it. sorry my mind did not work enough. thanks a lot Cdbl([myfield]/100) worked

Check again, is that the code I suggested ??? NO IT IS NOT!!!

It may work but it is WRONG to do it this way

 

[sunilvedula]

what is wrong. what am i doing is this. just dividing the amt by 100.

 

[namliam]

Yes got it. sorry my mind did not work enough. thanks a lot Cdbl([myfield]/100) worked

CDbl([yourtextfield])/100

NOTE the very important difference between your and mine solution
The fact that the /100 is INSIDE the () in your case is BIG.

This is causing an IMPLICIT conversion of MyField prior to de devision.
You should not allow implicit conversions instead you should use EXplicit conversions, which is what the CDBL does.

Thus the 'only' 'proper' way of doing it is
CDBL(MyField) / 100

First explicitly convert myfield to a 'real' number from the string it is now, then and only then devide by 100

 

[gemma-the-husky]

out of interest, if you divide by 100, how can $2145 become 214.5

i think what mailman is saying is that dividing 2145 by 100 FIRST, MAY result in integer division, giving you 21 as a result, not 21.45. converting the number first avoids the possibilty that that might happen.