CODEWITHSUNDEEP
vbams-access
Robert Patrician
Robert Patrician

Reputation: 322

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

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

I want the visual basic module to be able to do

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

Upvotes: 0

Views: 767

Answers (1)

Vlado
Vlado

Reputation: 888

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

Upvotes: 2

Related Questions