vba access Comparing two strings; Recordset with string

[Susy]

Hi,



I am trying to compare a recordset saved as a string with another string...

    If Right(Trim(rs![Company_Street]), 6) = "street" Then   ' like
        rs![Company_Street] = Replace(rs![Company_Street], "street", "str.")
    End If
        
    If Right(Trim(rs![Company_Street]), 6) = "Street" Then    'like
        rs![Company_Street] = Replace(rs![Company_Street], "Street", "Str.")
    End If

The problem is: Mainstreet and Main Street are equal when comparing.... and that is not the case...

An idea how to solve this... I tried "like" but it did not work.

I also tried to do a Select Case Statement...

Select Case rs![Company_Street]
     
     Case Is = Right(Trim(rs![Company_Street]), 6) = "street"          
             rs("Company_Street") = Replace(rs![Company_Street], "street", "str.")

      ....
      ....

End Select

But it did not work!

[rpeare]

welcome to the world of string comparison. This is why google has 013620478120348710238 servers and tons of computing power. The short answer here is that you are not going to be able to run a foolproof algorithm on your data with the computing power of access.

Another example would be something more arcane like

Water Street
Waterstreet Ave

so you can't even search for 'street' and replace it effectively

You could conceivably try to convert all instances of 'street' to str. but you would have to insert a space (" ") at the front of the string to force a break where one does not exist (as in your example) then replace all double spaces with single spaces to account for a forced space in strings that actually had them (replace(string, " ", " "))

then you'd have to do the same thing for st, st. but then how would you account for something like 1st Street you'd end up with something like 1 str. str.

I have yet to see an effective string parser for addresses built in access.

[orange]

Susy,

Not sure where you are located or what you real needs are, but for street types and abbreviations in Canada (or other)
see this guide from Canada Post.


Good luck

[Bulzie]

So that above code works except for values that do not have space before street? And as repeare said, if you data is not all standardized, any compare/replace will be risky. Make sure you have a backup of your data table before trying anything.

Maybe do a query and search for values that are like MainStreat and if not many, clean up beforehand. Something like

x: Mid(rs![Company_Street], Instr(1, rs![Company_Street], "street")-1, 1)

In criteria box of that field put <> " "

[Susy]

Wow, I need to test all these.... but why is Main Street equal to Main street?

thank you, for now ��