[ms-access] Access VBA | How to replace parts of a string with another string

I am trying to create a piece of code that replaces one word with another. Example: Replace Avenue with Ave and North with N. I am using MS Access, I could use SQL REPLACE Function but I want to do this in VBA using Access module so that I can attached the function to other column.

I am not sure where to start with this, so any input will be greatly appreciated.

Guy

I was reading this thread and would like to add information even though it is surely no longer timely for the OP.

BiggerDon above points out the difficulty of rote replacing "North" with "N". A similar problem exists with "Avenue" to "Ave" (e.g. "Avenue of the Americas" becomes "Ave of the Americas": still understandable, but probably not what the OP wants.

The replace() function is entirely context-free, but addresses are not. A complete solution needs to have additional logic to interpret the context correctly, and then apply replace() as needed.

Databases commonly contain addresses, and so I wanted to point out that the generalized version of the OP's problem as applied to addresses within the United States has been addressed (humor!) by the Coding Accuracy Support System (CASS). CASS is a database tool that accepts a U.S. address and completes or corrects it to meet a standard set by the U.S. Postal Service. The Wikipedia entry https://en.wikipedia.org/wiki/Postal_address_verification has the basics, and more information is available at the Post Office: https://ribbs.usps.gov/index.cfm?page=address_info_systems

Since the string "North" might be the beginning of a street name, e.g. "Northern Boulevard", street directions are always between the street number and the street name, and separated from street number and street name.

Public Function strReplace(varValue As Variant) as Variant

Select Case varValue

    Case "Avenue"
        strReplace = "Ave"

    Case " North "
        strReplace = " N "

    Case Else
        strReplace = varValue

End Select

End Function

You could use a function similar to this also, it would allow you to add in different cases where you would like to change values:

Public Function strReplace(varValue As Variant) as Variant

    Select Case varValue

        Case "Avenue"
            strReplace = "Ave"

        Case "North"
            strReplace = "N"

        Case Else
            strReplace = varValue

    End Select

End Function

Then your SQL would read something like:

SELECT strReplace(Address) As Add FROM Tablename