Unable to get Negative value from text

[Cap2010]

hi,

I am extracting numbers from a text file.
Below is the code.

Dim sValue as string
Dim dTotal as double

sValue = Mid(StringLine, 35, 9)

dTotal = sValue

' At dTotal it gives Error Type mismatch
' value in dTotal to be as -38234

sNumber has value as "000-38234"
How do I solve this problem ?

Thanks,

Cap

 

[MichaelRed]

hmmmmmmmmm,

the message is not all that clear. It 'LOOKS LIKE" the "000" part is an operand and the "-" is an operator and the "38234" is the second operand. But it sure is not "clear" that the pattern is consistiend (always two values with SOME arithmatic operator? Are the Vlaue parts the same ALWAYS length? Is the Operator always a "Minus"?

The only part which IS clear is that you extract 9 characters from some string and appear to want to convert the STRING to a value. An approach would be to "eval" the expression:

Public Function basEvalStr(StrIn As String) As Double

    basEvalStr = Eval(Mid(StrIn, 35, 9))

End Function

Which is obviously a trivial function which only works for the most specific instance of the nine character at the specific position being able to be "evaluated" as a numeric 'expression'. Most ANY circumstance not within the pattern necessary for a valid numeric expressin within the 9 character string WILL cause some error.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JohnYingling]

If you want to remove hyphens and consider the rest of the string as a number then try replacing the hypens with an empty string.
svalue = Replace(Mid(StringLine, 35, 9),"-","&quot

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[andylec]

I'm guessing the numbers in the text file are padded with zeros to get a fixed width format, hence a -ve sign in the middle.

Just needs something to trim the leading zeros.

rgds
Andy

 

[Cap2010]

hi,

Thanks, all.
Solved it, below is the function used. Michael Red, your function Eval is not available and it is mostly a VB Script function.

Purpose : To extract numbers from a text string and to transfer the value. It gives error when it comes to minus value. Below function checks the string "000-38234" into
-38234.

Function fEvalValue(vValue As Variant) As Double

Dim vValue2 As Variant
Dim vBlankChar As Variant
Dim i As Integer

vBlankChar = " "
For i = Len(vValue) To 1 Step -1
vBlankChar = Mid(vValue, i, 1)
If vBlankChar <> &quot;-&quot; Then
vValue2 = vBlankChar & vValue2
ElseIf vBlankChar = &quot;-&quot; Then
vValue2 = -vValue2
Exit For
Else
Exit For
End If
fLoop:
Next i

fEvalValue = vValue2
End Function

Cap2010