473,329 Members | 1,332 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,329 software developers and data experts.

removing special characters from money fields

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
Jul 19 '05 #1
2 4048
I suggest that you start just storing numeric values in your database
instead of monetary symbols and what not.
Function m(sMoneyString)
Dim s
''get rid of $
s = Replace(sMoneyString, "$", "")

''if ( exists, replace with a -
s = Replace(s, "(", "-")

''get rid of ) if it exists
s = Replace(s, ")", "")

''convert to a double datatype
m = CDbl(s)
End Function
If rs.Fields.Item(PdAmount).Value <> "" Then
total = total + m(rs.Fields.Item(PdAmount).Value
End If

''you don't have to worry about adding vs. subtracting, since substraction
would be the same as adding a negative number. That's what you'd wind up
here.

3 + -2 vs. 3 - 2

Ray at work


"Abby Lee" <ab*******@hotmail.com> wrote in message
news:80**************************@posting.google.c om...
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

Jul 19 '05 #2
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


Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Barry Olly | last post by:
Hi, I'm working on a mini content management system and need help with dealing with special characters. The input are taken from html form which are then stored into a varchar column in...
2
by: Paul | last post by:
Hi Doing my best to understand why I cant insert a vbcrlf or an equivalent into this piece of code that read messages from an SQL DB. The messages it takes read into a marquee fine but I want...
1
by: Phil Amey | last post by:
In a web based form I am able to make sure that there is text in an input field but I want to restrict the user from using such characters as ~ # & ' How can I modify this JavaScript below to...
2
by: Keith | last post by:
A2003, XP Pro. I'm writing a VBA procedure to remove wildcard characters from a number of fields in various tables. I'm calling a sub routine and passing in strings representing the character...
1
by: SStewart | last post by:
Does anyone know a good regex expression for removing all special characters from a string, allowing only alphanumeric?
1
by: ronrsr | last post by:
I have an MySQL database called zingers. The structure is: zid - integer, key, autoincrement keyword - varchar citation - text quotation - text I am having trouble storing text, as typed in...
1
by: ronrsr | last post by:
I have an MySQL database called zingers. The structure is: zid - integer, key, autoincrement keyword - varchar citation - text quotation - text I am having trouble storing text, as typed in...
1
by: amitpatil | last post by:
Hello friends, I have a web form which holds fields containing special characters. On the next page when user submits those details, Information is shown with adding slashes to special...
1
by: Krelek1000 | last post by:
Hi, Our Oracle Database is going to go through an update soon that will involve the looking of Special Characters in fields of a table. I am wondering if there is a special way to look for...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.