[mso] Re: Access & Vowels

an easy one he says...you know I've never written a module
before...sheesh!

So, it looked like I SHOULD be able to do it. I copied/pasted Jim's code
to the module builder, named it NoVowels. In a query I put it in the
field as a string like this:  stripped: sStripVowels([Street1]) - ran
the query and BAMM, just like emeril - kicked it up a notch or two.

This little baby is gonna help me resolve an issue with duplicate
entries in my getting-larger-every-day database.

suuh-wheeeet!!!=20

/glenda

PS. Just in case anyone's interested...

We do "blind" data entry. That is, data goes into the system, as clean
as we can make it, but we don't know if the record already exists or
not.  Considering we get data from several sources and our kids can be
part of any or all of them, this is a rather risky venture.  Needless to
say, we have a problem with duplicate records.  For example, we might
get a record for Cobelia C Smith or Coby C Smith or Cobelia Christine
Smith or C Christine Smith.  The address could be 421 N Woodland or it
might be 421 North Woodland. One record might have the DOB, the other
not.  Maybe Coby is using her father's address for some things and her
mother's for others...in separate states.  We have standard algorithims
in place that identify duplicate records based on
=20
the first 3 characters of the first name, first 3 of last name, city,
state
Or
The first 3 characters of the last name, DOB, city, State
Or
Several other common combinations of first, last, city, state, zip, dob

Still, dupes happen.

In the grand scheme of things, we do pretty well with our data cleansing
and have a less than .002 ratio of duplicates per 20,000 records.
However, it's one of those annoying little things that can influence
people's thinking.  If Coby Smith gets the same 3 pieces of mail, each
with a slightly different address, is she going to instinctively
understand we got her information from 3 different sources and smile
knowingly while sending us her money or is she gonna toss them all in
the trash thinking "these people can't get it right" and send her money
elsewhere?

We do the blind data thing to save time and (wo)man power. We have data
entry standards to ensure accuracy. During the course of daily operation
we run routine processes to weed out dupes that the system doesn't
catch.  The additional steps we're implementing now won't significantly
impact our daily operation and will ultimately save us a (small) few $$
in postage/return postage and a bit of embarrassement in communicating
with our target market.

Like Martha says...it's a good thing.

Thanks again everyone for the assistance. /glenda

-----Original Message-----
From: Jim Pettit [mailto:jimpettit@xxxxxxxxx]=20
Sent: Tuesday, July 26, 2005 11:37 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access & Vowels

Glenda--

Oh, finally, an easy one. ;-) Try this:
----------------------------------
Public Function sStripVowels(sIn As String) As String
    Dim asVowels() As Variant
    Dim iLoop As Integer
    sStripVowels =3D sIn
   =20
    asVowels() =3D Array("a", "e", "i", "o", "u", " ")

    For iLoop =3D 0 To UBound(asVowels)
        sStripVowels =3D Replace(sStripVowels, asVowels(iLoop),
vbNullString)
    Next iLoop
    sStripVowels =3D UCase(sStripVowels)

End Function
-----------------------------------

Note 1: you can remove any character just by placing it in place if the
vowels.
Note 2: if you don't wish to use uppercase, get rid of tht Ucase
function call.

--Jim

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: