Replace text in a field?

[Jim]

Hey all, I have a dilemma.

I have a field in a table called "Supervisor." The table currently
has 1,500+ records. Sometimes, the HR download that populates the
field formats the field one way, sometimes another. For example, I
may get records such as follows:

Supervisor
Hall,John J.
Hall, John J
Harrison, Marcie
Harrison,Marcie

So you see, sometimes there is a space after the comma, sometimes not.
Because additional data is uploaded weekly, I'll need to create some
code that runs through the list and changes them all to have spaces
following the comma. How do I fix this using code?

Thanks

[Tom van Stiphout]

On 15 Dec 2003 19:10:35 -0800, ji*************@hotmail.com (Jim)
wrote:

What do you have so far?
-Tom.

Hey all, I have a dilemma.

I have a field in a table called "Supervisor." The table currently
has 1,500+ records. Sometimes, the HR download that populates the
field formats the field one way, sometimes another. For example, I
may get records such as follows:

Supervisor
Hall,John J.
Hall, John J
Harrison, Marcie
Harrison,Marcie

So you see, sometimes there is a space after the comma, sometimes not.
Because additional data is uploaded weekly, I'll need to create some
code that runs through the list and changes them all to have spaces
following the comma. How do I fix this using code?

Thanks

[mork]

>> Hey all, I have a dilemma.

I have a field in a table called "Supervisor." The table currently
has 1,500+ records. Sometimes, the HR download that populates the
field formats the field one way, sometimes another. For example, I
may get records such as follows:

Supervisor
Hall,John J.
Hall, John J
Harrison, Marcie
Harrison,Marcie

So you see, sometimes there is a space after the comma, sometimes not.
Because additional data is uploaded weekly, I'll need to create some
code that runs through the list and changes them all to have spaces
following the comma. How do I fix this using code?

This is a commen problem and there are many algorithms around that'll
accomplish this task (with varying degrees of success).

The best way is to find a way around it. Could HR interact with the main
DB to search for a person before adding/modifying? Your essentially
matching records based on a text field, which is never good :)

However, it is sometimes unavoidable.. a couple of algorithms come to
mind, one is Soundex which matches strings based on the way they sound.
You create (and probably best to store it with each record) a soundex
key.. as new records come in, compute their soundex key and compare. Do
some searching on "soundex" and you'll find many examples written in
VB/VBA.. it's not a lot of code to accomplish it.

in your case where punctuation is a bigger concern, there is another
algorithm; "levenshtein length" - I believe the idea here is (and I could
be mistaken) to take two strings and score them based on how many
characters must be changed to make them equal. This would certainly catch
the examples you provided if you considered cases of levenshtein length
beings less than 2 as a threshold to be equal.

so, use that as a starting point in your search;
soundex
levenshtein length

good luck!

--
email: my*****@canada.com // my_nick = mork

[mork]

>> Supervisor

Hall,John J.
Hall, John J
Harrison, Marcie
Harrison,Marcie

So you see, sometimes there is a space after the comma, sometimes not.
Because additional data is uploaded weekly, I'll need to create some
code that runs through the list and changes them all to have spaces
following the comma. How do I fix this using code?

Sorry, I may have misunderstood your objective in my last post. I thought
you were bringing in records and wanted to find matching redcords based on
the name. If it's simply straightening up the format of the name you
seek, then doing some string manipulation is what you seek.

A regular expression would do the trick, of course, but you could do
something like replace all occurences of "comma followed by a space" with
"comma", then replace all occurences of "comma" with "comma followed by a
space"..

you'll need to do suqash it down to just comma's first to avoid ending up
with "Hall, John J" (note: 2 spaces). This can eb done in one line

strNameAfter = Replace(Replace(strNameBefore, ", ", ","), ",", ", ")

So, here's something I just tried and it seems to work for the examples
you provided:

Dim inNames As Variant
Dim outName As String

inNames = Array("Hall,John J.", _
"Hall, John J.", _
"Harrison, Marcie", _
"Harrison,Marcie")

For Each strName In inNames
outName = Replace(Replace(strName, ", ", ","), ",", ", ")
debug.print outName
Next strName

Output:
-------
Hall, John J.
Hall, John J.
Harrison, Marcie
Harrison, Marcie
is this what you were after? Hope it helps!

--
email: my*****@canada.com // my_nick = mork