Re: removing special characters from money fields
From: David C. Holley (DavidCHolley_at_netscape.net)
Date: 07/27/04
- Next message: Stefan Berglund: "Re: HTML Email"
- Previous message: Curt_C [MVP]: "Re: Display Managers and Employees Database"
- In reply to: Abby Lee: "removing special characters from money fields"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 27 Jul 2004 10:47:13 -0400
You could use a function like this to strip the characters out and to
detect if the value is positive or negative...
function stripSpecialCharacters(strString)
Dim newString
Dim nextCharacter
Dim i
stripSpecialCharacters = ""
nextCharacter = ""
For i = 1 To Len(strString)
nextCharacter = Mid(strString, i, 1)
If ((Asc(nextCharacter) > 47 And Asc(nextCharacter) <58) Or _
(Asc(nextCharacter) = 46) Then
newString = newString & nextCharacter
Next
stripSpecialCharacters = newString
end Function
The function will strip out all special characters with the exception of
periods. You'll probably need to use CStr() at the beginning and CCur()
at the end. But now that I think about it you could probably just use
CCur() to convert the value to a monetary value and then just add them
all up. CCur("(5.00)") should give you a negative number (haven't tried
it). So your function would be...
<% total = total + CCur(amount) %>
Another option would be to use the Sum aggregate function in a query.
(Assuming of course that the value is stored as a monetary value.)
David H
www.gatewayorlando.com
Abby Lee wrote:
> I need to add and subtract transactions for each of our accounts
> within our Access DB for account reconciliation.
>
> Values found in our PdAmount field are either positive or negative.
> Positive numbers are entered like $690.34
> Negative numbers are entered like ($42.36)
>
> I will use something like:
> <%
> Do until rs.eof
> If rs(PdAmount) <> "" Then
> 'need function to check for positive
> 'if positive...remove "$" and add to total
> 'if negative...remove "(", "$", ")" and subtract from total
> End If
> Response.Write(total)
> %>
>
> Can someone give me a function that I can use?
>
> Thanks
> Abby
- Next message: Stefan Berglund: "Re: HTML Email"
- Previous message: Curt_C [MVP]: "Re: Display Managers and Employees Database"
- In reply to: Abby Lee: "removing special characters from money fields"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
