[AccessD] Remove Punctuation

There is no built-in replace function in Access 97, so you're stuck with
clunky code, but the way I've always done this is to search for two
spaces and replace them with one.  If you iterate that until the
instr([textfield], "  ") {that's two spaces, BTW}  returns a zero, that
will get rid of the extra spaces.

Charlotte Foust

-----Original Message-----
From: Greg Smith [mailto:weeden1949 at hotmail.com] 
Sent: Thursday, January 22, 2004 8:57 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Remove Punctuation


Hello everyone!  Greetings from Frigid Iowa.

Access 97.

I need to remove punctuation from a single field in a table which has
about 1,500,000 records in it...so far.  It's only 4 columns wide, but
fairly lengthy.

A client has to have all punctuation removed from this particular field,
which is a text field.  I have code which removes the punctuation (as
Smith, Greg W. becomes Smith  Greg W ) but leaves a space in it's place.
Yes, it gets rid of the punctuation, but for future consistancy (from
now on, they are putting the data in without any punctuation) I can't
have the additional space where the "," or "." was or the search
routines will not find every instance of, for example, "Smith Greg W"
because "Smith  Greg W " isn't the same.

The code I'm using came from M$'s support, and it does work for removing
the characters that I want to remove.  However, if I use "" instead of "
", nothing gets changed.  It's "ACC: Sample Function to Replace Special
Characters", article #109825.

Is there a better way to do this?  I remember doing this several years
ago when I was first setting up their data, but it's been waaaaay too
many moons since then.

Any help would be greatly appreciated!

TIA!

Greg Smith
Weeden1949 at hotmail.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com