Format string as number

[DrSmyth]

Hi, I'm substringing a memo field to give me a number:

=RightTrim(SubStr(<Diary Narrative> ,19 ,50))

the result of this is a number but because of the way i've obtained it BO thinks it's a string. Is there a way of 'fooling' BO into thinking it's actually a number?

The reason for this is that i'm using it as a condition on an alerter i.e. where it's greater than a certain value..

Thanks,

Andy

 

[BrianTyler]

Try the ToNumber function. This will convert a string of digite to numeric, but the result may be shown as Floating Point because of the large size.

You may get somewhere by using LeftPad on your existing SubStr(inged) object to put leading zeros back on.

Brian

 

[DrSmyth]

Cheers Brian, used the ToNumber function

=ToNumber(<Amount>)*1000

where <amount> is the substring and it seems to work in most cases (although not sure why it's divided all the reults by 1000).

Luckily the case where it hasn't worked i.e. where the amount is 1 or a negative number I was looking to ignore anyway...

Thanks for the help

 

[DrSmyth]

In case anybody else uses this thread, I've solved the *1000 issue. Basically beacuse the string numbers were formatted with seperating commas (e.g. 1,000.00 or 3,456.00) these were being used as decimal points (all sounds a bit french to me)

This posed two problems:
1 - I had to multiply everything by 1000 to get the actual number
2 - If the amount was under 1,00 then there were no sperating commas so i actually ended up with the figure i was hoping to get multiplied by 1,00 (e.g. figure 48 - reported figure 48,000)

I got around it by using a replace to get rid of the commas:

Amount: = Replace(RightTrim(SubStr(<Diary Narrative> ,19 ,50)) ,"," ,"")
[\code]

used with:
[code]
Reportable amount: =ToNumber(<Amount>)

Hope this helps somebody...

 

[DrSmyth]

Sorry there's a typo above... where I've written 1,00 i meant to say 1,000