Removing commas from a field

  • Thread starter Thread starter Kathie G via AccessMonster.com
  • Start date Start date
K

Kathie G via AccessMonster.com

I have a table that gets exported to a *.csv or *.xls file (either is
acceptable) The problem is one of the fields contains an address, which some
have a comma (ie: 124 happy ville lane, PO Box 123) and I need to strip out
all commas and replace with a space. Is there an easy way for this to occur?
 
I have a table that gets exported to a *.csv or *.xls file (either is
acceptable) The problem is one of the fields contains an address, which some
have a comma (ie: 124 happy ville lane, PO Box 123) and I need to strip out
all commas and replace with a space. Is there an easy way for this to occur?
Click to expand...

If you have a version of Access that supports the Replace function,
you can use:
=Replace([FieldName],",","")

If you have a version that does not support Replace(), you need to
write a User Defined function to search each record for the comma and
remove it.
Place the following in a Module.

Public Function CommaOut(FieldIn As String) As String
Dim intX As Integer
Dim strNew As String
intX = InStr(FieldIn, ",")
If intX > 0 Then
strNew = Left(FieldIn, intX - 1) & " " & Mid(FieldIn, intX + 1)
End If
If intX > 0 Then
CommaOut = strNew
Else
CommaOut = FieldIn
End If
End Function

Add whatever error handling is needed.

You can call it from a query:
NewAddress:CommaOut([AddressField])
 
I have a table that gets exported to a *.csv or *.xls file (either is
acceptable) The problem is one of the fields contains an address, which some
have a comma (ie: 124 happy ville lane, PO Box 123) and I need to strip out
all commas and replace with a space. Is there an easy way for this to occur?
Click to expand...

If you have a version of Access that supports the Replace function,
you can use:
=Replace([FieldName],",","")

If you have a version that does not support Replace(), you need to
write a User Defined function to search each record for the comma and
remove it.
Place the following in a Module.

Public Function CommaOut(FieldIn As String) As String
Dim intX As Integer
Dim strNew As String
intX = InStr(FieldIn, ",")
If intX > 0 Then
strNew = Left(FieldIn, intX - 1) & " " & Mid(FieldIn, intX + 1)
End If
If intX > 0 Then
CommaOut = strNew
Else
CommaOut = FieldIn
End If
End Function

Add whatever error handling is needed.

You can call it from a query:
NewAddress:CommaOut([AddressField])
Click to expand...

I should have written
=Replace([FieldName],","," ")
to replace the comma with a space.
 
Kathie,
There may be a simpler method, but what fun would that be?
Run an update query against the table with an UpdateTo of... (use your
names)
IIF(InStr([Addr],",") = True, Left([Addr], InStr([Addr],",")-1) &
Mid([Addr],InStr([Addr],",")+1),[Addr])

If the Addr contains a comma this will concatenate the Left and Right
portions of the string without the comma.
If no comma, Addr is left as it's original value.

Backup your data, and test thoroughly before trying. I tested... should
be OK

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
I had problems getting the update query to work but ...I did get the module
to work. Thanks guys - your the best!

Al said:
Kathie,
There may be a simpler method, but what fun would that be?
Run an update query against the table with an UpdateTo of... (use your
names)
IIF(InStr([Addr],",") = True, Left([Addr], InStr([Addr],",")-1) &
Mid([Addr],InStr([Addr],",")+1),[Addr])

If the Addr contains a comma this will concatenate the Left and Right
portions of the string without the comma.
If no comma, Addr is left as it's original value.

Backup your data, and test thoroughly before trying. I tested... should
be OK

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
I have a table that gets exported to a *.csv or *.xls file (either is
acceptable) The problem is one of the fields contains an address, which
Click to expand...
[quoted text clipped - 3 lines]
all commas and replace with a space. Is there an easy way for this to
occur?
Click to expand...
Click to expand...
 
OK - weird enough - i got this to work as well (and now the module won't??)
go figure...my head hurts.

Al said:
Kathie,
There may be a simpler method, but what fun would that be?
Run an update query against the table with an UpdateTo of... (use your
names)
IIF(InStr([Addr],",") = True, Left([Addr], InStr([Addr],",")-1) &
Mid([Addr],InStr([Addr],",")+1),[Addr])

If the Addr contains a comma this will concatenate the Left and Right
portions of the string without the comma.
If no comma, Addr is left as it's original value.

Backup your data, and test thoroughly before trying. I tested... should
be OK

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
I have a table that gets exported to a *.csv or *.xls file (either is
acceptable) The problem is one of the fields contains an address, which
Click to expand...
[quoted text clipped - 3 lines]
all commas and replace with a space. Is there an easy way for this to
occur?
Click to expand...
Click to expand...
 
Back
Top