SQL Left Trim / to number

[tomk01]

How do I trim the first charectar from a field. I am using crystal 8.5 (I.e.."EstAnn" is a text filed but I want it to be a number filed. Most of the fields look like this "$52425.45" however some fileds look like this "Pending EE's" (number of charectar very in filed) how do get rid of the first "$" or what ever else is in the filed and make it a number field?)

 

[bdreed35]

If you use the val function around it, it should return the numeric value:

val("$1,2345.67") returns 12345.67

You can also use the mid function inside of a tonumber funciton:

ToNumber(Mid({table.field},2))

~Brian

 

[synapsevampire]

Try:

// substitute your field for the "1234.56"
if isnumeric("$1234.56") then
val(mid("$1234.56",2))
else
0

Keep in mind that a formula cannot return both a numeric adn a string in the same formula, so if you want to show the non-numeric value, you would use another formula to display it and conditionally suppress each based on the above being zero.

-k

-k

 

[LikePork]

Maybe
Dim tempStr as String
Global tempNum as Number
tempStr=Replace({table.EstAnn},"$","")
if isNumeric(tempStr) then
tempNum=CDbl(tempStr)
end if
formula = tempStr


hmm, I'm using for VB .net so teh syntax is different, also now you're in trouble because the value returned form a formula must be either Number or String. So, I'd suggest returning a string of digits, use a global variable if you need the number for some kind of total.