Trailing "-" for Neg Number

[John]

I am trying to easily convert a string into a number. The string uses a
trailing "-" to indicate a negitive number. What I currently have is:

Dim MyNum as Single
Dim RawNum as String

RawNum = "The value we need is 654,169- and it is neg."
MyNum = Val(Mid(RawNum, 21, 8)) * IIf(Mid(RawRec, 29, 1) = "-", -1, 1)

My concern is that if a number on the report gets off by just one digit, my
code falls apart. Is there a better way to deal with this?

 

[Marshall Barton]

I am trying to easily convert a string into a number. The string uses a
trailing "-" to indicate a negitive number. What I currently have is:

Dim MyNum as Single
Dim RawNum as String

RawNum = "The value we need is 654,169- and it is neg."
MyNum = Val(Mid(RawNum, 21, 8)) * IIf(Mid(RawRec, 29, 1) = "-", -1, 1)

My concern is that if a number on the report gets off by just one digit, my
code falls apart. Is there a better way to deal with this?

No way to do that easily.

First you have to find the start of the number and then find
the end of the number. With those, you can use Mid to
extract it. Then use Replace to remove the comma and Val to
get the value of the number. The position of the end of the
number can also be used to check for the minus sign.

Here's some air code to give you the general idea:

Dim pos As Long
Dim numstart As Long
Dim numend As Long

If RawNum Like "*#*" Then
For pos = 1 To Len(RawNum)
If Mid(RawNum, pos, 1) Like "#" Then
numstart = pos
Exit For
End If
Next pos

For pos = numstart + 1 To Len(RawNum)
If Not Mid(RawNum, pos, 1) Like "#" Then
numend = pos
Exit For
End If
Next pos

MyNum = IIf(Mid(RawRec, numend, 1) = "-", -1, 1) _
* Val(Replace(Mid(RawNum, numstart, _
numend - numstart), ",", ""))
Else
MyNum = 0
End If

 

[John Spencer]

First of all, I believe that Val will stop converting the number at the comma.
So that means you are going to get -654 and not -654,169.

It seems to me that you are going to need a custom VBA function to reliably
extract the number. Your code will have to test for the presence of a number
in the string. (What do you do if there are two numbers in the string?)

So there is no EASY way to extract the number.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Daniel Pineault]

You could do something along the lines of


Function GetNumber(sStr As String)
On Error GoTo Error_Handler

'sStr = "The value we need is 654,169- and it is neg."
GetNumber = StripAllChars(sStr)
If Right(GetNumber, 1) = "-" Then
GetNumber = CDbl(Left(GetNumber, Len(GetNumber) - 1)) * -1
End If

Error_Handler_Exit:
On Error Resume Next
Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: GetNumber" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function


Public Function StripAllChars(strString As String) As String
'Strip the alphabetical characters from a string to leave only numeric and
'-' characters
Dim lngCtr As Long
Dim intChar As Integer

For lngCtr = 1 To Len(strString)
intChar = Asc(Mid(strString, lngCtr, 1))
If intChar >= 48 And intChar <= 57 Or intChar = 45 Then
StripAllChars = StripAllChars & Chr(intChar)
End If
Next lngCtr
End Function

but even this can fail if there is a hyphenated word in your text. If you
will never have a hyphenated word then it should work fine.

You could also go through your string character by character to identify the
start and end positions of the numeric text and then capture that string (+1
character at the end for the-) and then perform a trim(). Then if the last
char is still -, you have a negative otherwise you have your value.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[John]

John - you are absolutly correct. My value of 18,362,032 ended up being 18.
That's quite a budget cut!

I'll work with what Daniel Pineault and Marshall Barton posted to see if I
can work something out...

Thanks,
John

 

[John]

Thanks Daniel - very helpful... I'll play with this and see what I can come
up with. I'll post if any problems...

 

[DrGUI]

Try the following:

iNewValue = iif(instr("18,362,032-","-"),"-","") &
format(val(replace("18,362,032-",",","")),"#,###")