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
Use Access's VBA function Replace(text, find, replacement):
Dim result As String
result = 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 Select
End Function
Then your SQL would read something like:
SELECT strReplace(Address) As Add FROM Tablename
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
DECLARE @Mymessage varchar(100) SET @Mymessage = ‘I do not know how to do this in VBA. ‘ SELECT REPLACE(@Mymessage, ‘know’, ‘understand’)Again, I am not sure how to write this in VBA in a module. Thanks for your time. – Asynchronous