Re: Need to remove all spaces and special characters from a field

[John Nurick]

Hi Ken,

One simple way is to use the rgxReplace() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm

This
rgxreplace("Lewis, County of", "[\W]", "")
deletes all "non-word" characters, i.e. everything except alphanumeric
characters and underscores. If you want to lose the underscores too, use
[_\W]

 

[Guest]

John,
I'm really quite a novice with Access, but I did get this function to do
what I needed by running it 3 times, First removing the spaces, then the
commas, then everything else. I've never done a module before!!

Thanks for the help. I only hope copying the test database I did tonight at
home will capture everything I need to do it at work!!

Thanks to you and everyone else who took a look at this. You guys are the
best!!!

Ken K. --
akkrug

Hi Ken,

One simple way is to use the rgxReplace() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm

This
rgxreplace("Lewis, County of", "[\W]", "")
deletes all "non-word" characters, i.e. everything except alphanumeric
characters and underscores. If you want to lose the underscores too, use
[_\W]

Is there a way to remove all spaces and special characters from a field in an
Access database or Excel spreadsheet so the field? For example, Change the
expression Lewis, County of to LewisCountyof. Please let me know if this
can be done.

Thanks for the help!

Ken K.

 

[John Nurick]

Ken,

You don't need to run it 3 times. Using
[\W]
as the pattern finds *all* spaces, punctuation and special characters,
in fact everything except letters, numbers and underscores.

John,
I'm really quite a novice with Access, but I did get this function to do
what I needed by running it 3 times, First removing the spaces, then the
commas, then everything else. I've never done a module before!!

Thanks for the help. I only hope copying the test database I did tonight at
home will capture everything I need to do it at work!!

Thanks to you and everyone else who took a look at this. You guys are the
best!!!

Ken K. --
akkrug

Hi Ken,

One simple way is to use the rgxReplace() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm

This
rgxreplace("Lewis, County of", "[\W]", "")
deletes all "non-word" characters, i.e. everything except alphanumeric
characters and underscores. If you want to lose the underscores too, use
[_\W]

Is there a way to remove all spaces and special characters from a field in an
Access database or Excel spreadsheet so the field? For example, Change the
expression Lewis, County of to LewisCountyof. Please let me know if this
can be done.

Thanks for the help!

Ken K.

 

[John Spencer]

As a guess, the query has two (or more) tables with a field named
Description. So the query can't tell which of the fields named description
it is supposed to pass to the function.

Try adding the table name to the field name

rgxreplace[YourTableName].([Description]," ","")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

This ran very well on my pc at home. There I am running windows Xp. I
copied
it to a cd and when I run it at work under windows 2000 professional, I
get
the following error: Ambiguous name in query expression
'rgxreplace([Description]," ","")'.

When I press F1 for help, nothing seems to happen. Any ideas?

Thanks!!

Ken K.
--
akkrug

Ken,

You don't need to run it 3 times. Using
[\W]
as the pattern finds *all* spaces, punctuation and special characters,
in fact everything except letters, numbers and underscores.

John,
I'm really quite a novice with Access, but I did get this function to do
what I needed by running it 3 times, First removing the spaces, then the
commas, then everything else. I've never done a module before!!

Thanks for the help. I only hope copying the test database I did
tonight at
home will capture everything I need to do it at work!!

Thanks to you and everyone else who took a look at this. You guys are
the
best!!!

Ken K. --
akkrug


:

Hi Ken,

One simple way is to use the rgxReplace() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm

This
rgxreplace("Lewis, County of", "[\W]", "")
deletes all "non-word" characters, i.e. everything except alphanumeric
characters and underscores. If you want to lose the underscores too,
use
[_\W]



On Fri, 19 Jan 2007 12:37:26 -0800, akkrug

Is there a way to remove all spaces and special characters from a
field in an
Access database or Excel spreadsheet so the field? For example,
Change the
expression Lewis, County of to LewisCountyof. Please let me know
if this
can be done.

Thanks for the help!

Ken K.