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

ms-access vba
Asynchronous picture Asynchronous · Dec 18, 2011 · Viewed 215k times · Source

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

Answer

Mitch Wheat picture Mitch Wheat · Dec 18, 2011

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

Dim result As String

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

Related questions

Code to loop through all records in MS Access

I need a code to loop through all the records in a table so I can extract some data. In addition to this, is it also possible to loop through filtered records and, again, extract data? Thanks!

Read More
Get length of array?

I'm trying to get the length of an array, yet I keep getting this error: Object required Am I doing something wrong? Dim columns As Variant columns = Array( _ "A", "ID", _ "D", "Name") Debug.Print columns.Length ' Error: Object required

Read More
How to add default signature in Outlook

I am writing a VBA script in Access that creates and auto-populates a few dozen emails. It's been smooth coding so far, but I'm new to Outlook. After creating the mailitem object, how do I add the default signature to …

Read More