Use VBA in MS Access to do multiple functions of REPLACE?

Question

I'm working on a project cleaning up addresses and need to create about 40+ rules for fixing them. What I need is a Microsoft Access file with a query that calls a visual basic module to run multiple REPLACE commands on a string of data.

Example: Let's say the data strings look like this

123 Fake Str.
345 Fake Street

I want the visual basic module to be able to do

Replace "Str." with "ST"
Replace "Street" with "ST"
Replace "Road" with "RD"

and so on. Once I have that, I can copy the structure over to the actual data. However I'm at the limit of my knowledge on how to call a VBA function in a query, and how to get the VBA function to export a value that the query can read. Any help would be much appreciated

Is the data in fields of a table? And I'm hoping you are not suggesting that you need a UDF approach to this. — waqasahmed
– waqasahmed, Commented Aug 3, 2020 at 22:00
Yes, the fields are in a table. My challenge is that to do this in SQL would involve either 50 queries that feed sequentially, or function so deeply nested that I couldn't claw my way out. — Robert Patrician
– Robert Patrician, Commented Aug 3, 2020 at 22:06

Vlado · Accepted Answer · 2020-08-03 22:20:05Z

2

I am not sure if I understand your problem right because it is a very simple task:

sql = "SELECT MyReplaceFunction([MyAddressField]) FROM MyTable"

put a public function in a module like this:

Public Function MyReplaceFunction(FieldValue As String) As String
    FieldValue  = Replace(FieldValue,"Str.","ST")
    FieldValue  = Replace(FieldValue,"Street","ST")
    FieldValue  = Replace(FieldValue,"Road","RD")
' continue like that with all your criteria
    MyReplaceFunction = FieldValue  
End Function

answered Aug 3, 2020 at 22:20

Vlado

8767 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robert Patrician Over a year ago

Oh my gosh thank you, this is going to save me the nightmare of nested replace functions or 50 separate replace queries, I was dreading having to do that

waqasahmed Over a year ago

@RobertPatrician Just to make you aware, performance-wise and for debugging, this is not great. Also this is not changing the underlying data, but rather presenting "a cleaner form" of the data each time the data is presented. If you want to perform a clean up of the data, then I recommend you take a different approach.

Collectives™ on Stack Overflow

Use VBA in MS Access to do multiple functions of REPLACE?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related