Trailing "-" for Neg Number

  • Thread starter Thread starter John
  • Start date Start date
J

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?
 
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
 
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 - 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
 
Thanks Daniel - very helpful... I'll play with this and see what I can come
up with. I'll post if any problems...
 
Try the following:

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