Access VBA | How to replace parts of a string with another string

Use Access's VBA function Replace(text, find, replacement):

Dim result As Stringresult = Replace("Some sentence containing Avenue in it.", "Avenue", "Ave")

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

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 SelectEnd Function

Then your SQL would read something like:

SELECT strReplace(Address) As Add FROM Tablename