Need help with writing a Function

[Desilu via AccessMonster.com]

I need to create a function to store in a module that manipulates a table
field in a query. The table field is text, with an additional informational
character stored on the far right. That character is either a “-“ or a “0â€.
The “-“ denotes that the text number should be multiplied by a negative .01.
If the last character on the far right is a “0â€, then the text number should
be multiplied by a positive .01. Of course the far right character is to be
removed before any multiplication.

Example of two possible values stored in the table:

00000001234-
000000012340

I want the function to take the values and turn them into positive/negative
decimals:

From 00000001234- to -12.34
From 000000012340 to a positive 12.34

Below is the formula I used in the query pane field that accomplishes this.
How do I write a function to store in a module to do the same thing?

Expr1: IIf(Right(Trim([MyTextNumField]),1)="-",-0.01*Left(Trim(
[MyTextNumField]),Len(Trim([MyTextNumField]))-1),0.01*Left(Trim(
[MyTextNumField]),Len(Trim([MyTextNumField]))-1))

I’m new with writing VBA functions, so any help would be greatly appreciated.

Thank you.
Desilu

 

[Douglas J. Steele]

Function ConvertNumber(InputString As String) As Single

Dim strInput As String

strInput = Trim(InputString)

II Right(strInput,1)= "-" The
ConvertNumber = -0.01*CSng(Left(strInput,Len(strInput)-1))
Else
ConvertNumber = 0.01*CSng(Left(strInput,Len(strInput)-1))
End If

End Function

 

[Desilu via AccessMonster.com]

Thank you Douglas; that worked. for the text 000000012340, it returns 12.
3400001525879 with trailing decimal. I shouldn't ask, but is there a round
function (which I should know) that would just return the 12.34?

Thanks so much!!!
Desilu

Function ConvertNumber(InputString As String) As Single

Dim strInput As String

strInput = Trim(InputString)

II Right(strInput,1)= "-" The
ConvertNumber = -0.01*CSng(Left(strInput,Len(strInput)-1))
Else
ConvertNumber = 0.01*CSng(Left(strInput,Len(strInput)-1))
End If

End Function

I need to create a function to store in a module that manipulates a table
field in a query. The table field is text, with an additional

[quoted text clipped - 33 lines]

Thank you.
Desilu

 

[Douglas J. Steele]

Well, that is an unfortunate fact of life using floating point numbers with
computers.

You could try wrapping the Round() function around the calculation

Function ConvertNumber(InputString As String) As Single

Dim sngCalculation As Single
Dim strInput As String

strInput = Trim(InputString)

II Right(strInput,1)= "-" The
strCalculation = -0.01*CSng(Left(strInput,Len(strInput)-1))
Else
strCalculation = 0.01*CSng(Left(strInput,Len(strInput)-1))
End If

ConvertNumber = Round(strCalculation, 2)

End Function

Even doing that, though, it's still possible that extra digits may show up.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Douglas; that worked. for the text 000000012340, it returns 12.
3400001525879 with trailing decimal. I shouldn't ask, but is there a round
function (which I should know) that would just return the 12.34?

Thanks so much!!!
Desilu

Function ConvertNumber(InputString As String) As Single

Dim strInput As String

strInput = Trim(InputString)

II Right(strInput,1)= "-" The
ConvertNumber = -0.01*CSng(Left(strInput,Len(strInput)-1))
Else
ConvertNumber = 0.01*CSng(Left(strInput,Len(strInput)-1))
End If

End Function

I need to create a function to store in a module that manipulates a table
field in a query. The table field is text, with an additional

[quoted text clipped - 33 lines]

Thank you.
Desilu

 

[Desilu via AccessMonster.com]

Okay. Thank you!
Desilu

Well, that is an unfortunate fact of life using floating point numbers with
computers.

You could try wrapping the Round() function around the calculation

Function ConvertNumber(InputString As String) As Single

Dim sngCalculation As Single
Dim strInput As String

strInput = Trim(InputString)

II Right(strInput,1)= "-" The
strCalculation = -0.01*CSng(Left(strInput,Len(strInput)-1))
Else
strCalculation = 0.01*CSng(Left(strInput,Len(strInput)-1))
End If

ConvertNumber = Round(strCalculation, 2)

End Function

Even doing that, though, it's still possible that extra digits may show up.

Thank you Douglas; that worked. for the text 000000012340, it returns 12.
3400001525879 with trailing decimal. I shouldn't ask, but is there a round

[quoted text clipped - 22 lines]