Format Text to Number

[holgar]

Hi,
I have a table(imported)with numbers like
28210.933 and .371.
What I want is 28.210,933 and 0,371.
I have tried xpr1: CDbl(CLng([aa]*100)/100) and it works fine with the first number but I get error on .0371.

Thanks

 

[bhoran]

You could try wrapping the field name in Val().

 

[koresnordic]

you get the error because it doesn't know which decimal point is correct. The one before 371 or the one after. You could check if the last charecter is a decimal point, and if so remove that charecter and then get the value. e.g temp: IIf(Right([field],1)=".",Left([field],Len([field]-1)),[field]) should remove last charecter if it is a decimal point.



Graham

 

[holgar]

Val works
Thanks to both of you

 

[holgar]

Ooops. Here is another unexpected problem.
Again imported table (I can't do anything about it) with
a column where numbers are like 00000043,580000 and
0.8962. Val function doesn't work on both numbers neither other formatting does.
If it could help numbers are related to a column.Decimal numbers are related to DKK (currency) and the others to USD.

Thanks

 

[bhoran]

What should the numbers be? 00000043,580000 doesn't look like a number at all. If they are numbers in the source I wonder if they are being imported incorrectly. Maybe you could look at the source of the data and import using a different delimiter or even fixed width.

 

[koresnordic]

My option will deal with the second problem, but how is the first problem to be dealt with? is the number 43 or 580000 or 43.58? or something else.



Graham

 

[holgar]

You are right. It is badly imported, but you now, it is sometimes easier to find workaround then to grip the real problem.

00000043,580000 is in real 43,58 and 0.8962 is 0,8962
because i'm from Europe (comma as decimal).

Fortunatly I've build this code to tacle the problem:
1 variant:
STANDARDCOSTPRICE: Val(Replace([STANDARD_COST_PRICE];",";".&quot)

2 variant:
STANDARDCOSTPRICE: CDbl(Replace([STANDARD_COST_PRICE];".";",&quot)

Thanks for trying to help