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

Question

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

User · Accepted Answer

Use Access s VBA function Replace text  find  replacement    Dim result As String  result   Replace  Some sentence containing Avenue in it     Avenue    Ave

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to ms-access

Examples related to vba