String replace doesn't always work (1 Viewer)

[mboe]

I have a function that is run as part of a query for scrubbing addresses in order to eliminate duplicates. To help find dups I get rid of things like ST and RD because they are not always included in the address. I thought it was working but noticed that in some cases, and I can't figure out why, it does not.

Here is one of my functions:

Public Function ScrubAddress(ByVal strfield As String) As String
If strfield <> "" Then
strfield = Replace(strfield, " ST ", " ")
strfield = Replace(strfield, " DR ", " ")
strfield = Replace(strfield, " RD ", " ")
strfield = Replace(strfield, " Ave ", " ")


strfield = Replace(strfield, " ", " ") ' Remove 10 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 9 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 8 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 7 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 6 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 5 spaces
strfield = Replace(strfield, " ", " ") ' Remove 4 spaces
strfield = Replace(strfield, " ", " ") ' Remove 3 spaces
strfield = Replace(strfield, " ", " ") ' Remove 2 spaces

strfield = Trim(strfield) 'Trim leading and trailing spaces
ScrubAddress = strfield
Else
ScrubAddress = ""
End If

End Function

It is suppose to find and remove all the ST and other items but I still find them exactly as I have defined them (with a leading and trailing space to make sure it's not part of a larger string).

I have tried everything I could think of, searched google, and this forum with no luck. Hopefully some one can help me out.

 

[RuralGuy]

What version of Access are you using. Can you post your db where you use this function so we can take a look at it?

 

[mboe]

I am using 2003 but I can't post the database because of privacy issues

 

[RuralGuy]

In that case show us the code where you are using the function you posted please.

 

[mboe]

Here is the query I was using it in.

SELECT [03_Address_List_Household].testpobox, [03_Address_List_Household].AddressID, [03_Address_List_Household].Address, [03_Address_List_Household].City, [03_Address_List_Household].State, [03_Address_List_Household].Zip, [03_Address_List_Household].Address_Full, [03_Address_List_Household].Address_Short, [03_Address_List_Household].pobox, [03_Address_List_Household].street, [03_Address_List_Household].teststreet, [03_Address_List_Household].scrubaddress
FROM 03_Address_List_Household
WHERE ((([03_Address_List_Household].testpobox) In (SELECT [testpobox] FROM [03_Address_List_Household] As Tmp GROUP BY [testpobox] HAVING Count(*)>1 )))
ORDER BY [03_Address_List_Household].testpobox;

 

[RuralGuy]

You are treating the function as if it is one of the fields in the [03_Address_List_Household] table, not as a function. Which field did you want to "scrub"

 

[mboe]

Sorry about that I think I posted the wrong query. The field I am scrubbing is teststreet.

INSERT INTO 02_Address_List_Single_CIF ( CIF_NO, ADDRESS_NO, Address, City, State, Zip, Test_Full, POBox, Street, TestPOBox, TestStreet, ScrubAddress )
SELECT [01_Address_list_Full].CIF_NO, [01_Address_list_Full].ADDRESS_NO, [01_Address_list_Full].Address, [01_Address_list_Full].City, [01_Address_list_Full].State, Left([zip],5) AS Zipcode, [address] & " " & [city] & " " & [state] & " " & [zip] & " " & [cif_no] AS TestFull, [01_Address_list_Full].POBox, [01_Address_list_Full].Street, [01_Address_list_Full].Testpobox, [01_Address_list_Full].teststreet, scrubaddress([address]) & " " & [city] & " " & [state] AS ScrubAddress1
FROM 01_Address_list_Full;

 

[WayneRyan]

mboe,

You're not too clear about WHAT isn't being processed properly.

Your function will not remove:

Ave. <-- " Ave "
DR. <-- " DR "
RD.
ST.

None of the above match the "pattern".

Wayne

 

[georgedwilkinson]

Why not apply a Jaro-Winkler algorithm to the field?

 

[mboe]

Sorry if I didn't make it clear.

It does not always find and replace strings like the following:

strfield = Replace(strfield, " ST ", " ")
strfield = Replace(strfield, " DR ", " ")
strfield = Replace(strfield, " RD ", " ")
strfield = Replace(strfield, " Ave ", " ")

So it will get rid of 95% of a string like " ST " but why doesn't it seem to do 100%. I have double checked the data and the ones it doesn't replace don't seem any different then the ones it does replace.

 

[mboe]

George,

Could you give me a practical example in VBA?

 

[datAdrenaline]

It may be important to understand that, by default, the Replace() function IS case sensitive ... you should probably form your Replace() like this ...

Replace(strField," ST ","",1,-1,vbTextCompare) {note vbTextCompare = 1 is you want to use literal numbers}

Also ... with your code ...

This block ...

strfield = Replace(strfield, " ", " ") ' Remove 10 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 9 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 8 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 7 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 6 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 5 spaces
strfield = Replace(strfield, " ", " ") ' Remove 4 spaces
strfield = Replace(strfield, " ", " ") ' Remove 3 spaces
strfield = Replace(strfield, " ", " ") ' Remove 2 spaces

Can be reduced to something like this ...

Do Until InStr(1, strField, "  ") = 0
    strField = Replace(strField, "  ", " ")
Loop

Hope that helps!

 

[RuralGuy]

This is not the problem but it looks like these lines will not accomplish anything except waste time. They are replacing 1 space with 1 space.

strfield = Replace(strfield, " ", " ") ' Remove 10 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 9 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 8 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 7 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 6 Spaces
strfield = Replace(strfield, " ", " ") ' Remove 5 spaces
strfield = Replace(strfield, " ", " ") ' Remove 4 spaces
strfield = Replace(strfield, " ", " ") ' Remove 3 spaces
strfield = Replace(strfield, " ", " ") ' Remove 2 spaces

 

[georgedwilkinson]

Why not apply a Jaro-Winkler algorithm to the field?

Well, you'd need to write a Jaro-Winkler function (or find one on the interweb thingy) and loop through one recordset with an inner loop on the second recordset.

Now, I happen to have a pre-written jaro() function that I got from somewhere but I don't know who to attribute it to so I'm loathe to post it publicly. If you're interested in the one I have/have modified, PM me with your e-mail address and I'll send it.

Additionaly, I have code that I have written implementing the aforementioned jaro function but it is a bit proprietary (I'm not worried about most people, just our competitors). I'd be happy to share a sample of that with you just so you can see how I use it.

I'd like to point out that using a distance algorithm is complex and cpu intensive. Let me know.

 

[datAdrenaline]

Allan ..

What you see is a victim of the editor ...

Replace(strField, "          ", " ")

Becomes ...
Replace(strField, " ", " ")

When OUTSIDE of CODE tags ...

 

[RuralGuy]

Thanks Brent! I should have guessed that but...

 

[mboe]

Thanks Brent,

I think putting the vbTextCompare did it for me. Thanks a ton...

Thanks for the cleaner code on the space replacement as well. I was meaning to get around to that after I fixed the first problem.