I just had to have a moan about the Val function in LotusScript. It causes me so much hassle.
The idea behind “Val” is to convert a String expression into a Numeric. So far so good, but so many people include the “,” in a number to separate the thousands. Others include a currency symbol. If Val hits any of this it returns surprising results.
How dumb is that
So,
Val(“1,234”) Returns 1
Val(“£1234”) Returns 0
So I find myself writing a quick function to strip out “,” and currency symbols
!
Subject: Tell it to Microsoft
LotusScript was designed to contain the same built-in functions as MS Visual Basic.Use CDbl, which at least understands commas correctly. Currency symbols you still have to deal with yourself.
Incidentally, there are also international issues as far as which character is the decimal point and which the thousands separator, that Val doesn’t deal with.
Subject: Dumb “Val” function in LotusScript
Hey David -I feel your pain – trying to deal with strings as numerics can be a hassle. To state the obvious, it’s best to stick with the intended data type for form fields when possible. That said, the “Val” function is performing exactly as documented.
From Designer help:
Val ( stringExpr )
Elements
stringExpr
Any string expression that LotusScript can interpret as a numeric value. It can contain any of the following kinds of characters.
Digits (0 1 2 3 4 5 6 7 8 9)
Other characters in hexadecimal integers (a b c d e f A B C D E F)
Sign characters (+ -)
Decimal point (.)
Exponent characters (E e D d)
Prefix characters in binary, octal, and hexadecimal integers (& B O H)
Suffix type characters (% & ! # @)
Return value
Val returns the converted part of stringExpr as a Double.
Usage
Val strips out spaces, tabs, carriage returns, and newlines from stringExpr. It starts converting from the beginning of the string and stops when it encounters a character other than those listed for stringExpr in the preceding list.
Thus, “1,234” converts to 1 and “£1234” converts to 0 because neither the “,” nor the “£” is defined in the list of characters it replaces.
A suggestion:
If these values are being accessed from a document (sounds as if they are) and, for some reason, you cannot use the intended datatype, it might be more efficient to remove the non-numeric characters through an input translation versus additional script code. The formula below, which took all of about 2 minutes to write, should work to remove non-numeric characters.
Tmp01 := “1,234”;
Tmp02 := “£12345”;
ThisVal := Tmp02;
Translation := “”;
@For(n := 1; n <= @Length(ThisVal); n := n + 1;
ThisChar := @Right(@Left(ThisVal; n); 1);
@If(
@IsError(@ToNumber(ThisChar));
“”;
Translation := Translation + @Text(ThisChar)
)
);
@StatusBar("New Value = " + Translation)
hope that helps,
dgg
Subject: Dumb “Val” function in LotusScript
The REAL answer is to store the pure numbers.
If people want to see currency and separators, do it in the display, NOT in the stored data.
This applies in spades to dates 