Address InStr Search and Replace (1 Viewer)

[MisB7]

So...I'm a VBA & Vb.Net newb, but I feel like I might be on the right track...or at least I'm firmly hacking into the weeds at this point.

I have this lovely work-related problem which my Vb.Net instructor has been advising me on...but sadly I'm a bit stuck (Note: I'm taking a beginner .Net class, HOWEVER I'm VERY MUCH hoping to avoid working towards my teachers proposed solution--which is call my 2007 Access db using ODBC into Visual Basic Explorer and write a Sub procedure w/ a dynamic String array(i.e. ReDim) + a 'For' loop + a 'Replace' function...and ::sigh:: that just seemed like a lot of code for what I felt SHOULD be a simple VBA fix in the Access IDE).

To be simple the problem is this:

I have an Address field in a Access 2007 database that looks like so:

| OBJECTID | Address | Address1 |
| 1 | 12395 FOLSOM BLVD |12395 FOLSOM BLVD |
| 2 | ZZ Fiirst Street | ZZ Fiirst Street |
| 3 | 346 First ST | 346 First ST |
| 4 | 12395 Harris BOULEV | 12395 Harris BOULEV |
| 5 | 346 First Street | 346 First Street |

See the Dup (3 & 5)? I need to Normalize this mess...but that can't be done till I do something about the Abbreviation (and misspelling) mismatches in the street designation (i.e. ST = Street, etc).

I read up on the VBA's REPLACE() function, and it appears that it would do a substring search-and-replace...which I thought meant I could just write a custom VBA function and query it with SQL and modify the Address1 field (which is my duplicate field of Address...want to be able to verify after the query is run).

Here is what my brain (and some internet searching) settled for:

Option Compare Database
'specifies the way in which strings are compared
Option Explicit
'forces me to declare all variables before using them

Public Function SearchIt()

Dim MyString As String
Dim OrigWord As String
Dim ReplaceWord As String
Dim PositionWord As Integer

OrigWord = " BLVD"
ReplaceWord = " BOULEVARD"
PositionWord = InStr(1, MyString, OrigWord, vbTextCompare)
'Trying to say if the OrigWord is NOT equal to the ReplaceWord (by virtue of the InStr search coming up blank)
'then you must Replace it with ReplaceWord
If PositionWord <> 0 Then
MyString = Replace(MyString, OrigWord, ReplaceWord, , , vbTextCompare)
End If
End Function

With this (not working...missing an Operator in 'SearchIt'"") SQL statement:

SELECT Test_address.ID, Test_address.OBJECTID, Test_address.Address, SearchIt""[Address1]"" AS Test_address.Address1
FROM Test_address;

I've been pouring over the only text I have for Access VBA work (Michael Vine's "Microsoft Access VBA Programming, III ed.")...but other then that I have not much guidance here (because, as you can tell by my .Net instructors guidance, I'm basically suppose to be doing this in the Explorer IDE in .NET as far as he's concerned).

So, my questions are:

1) Can a custom multiple substring search and replace function (and I feel like it's a function because I'm changing the string with the REPLACE) be written in VBA using the Access IDE (and avoiding this Sub + ReDim Array Loop .Net statement my instructor has me working on...which still seems unnessasary, but I guess the effort won't hurt me. If anyone can, can you please please explain why I need a For Loop and a Array to do this)?

2) IF SO, am I on the wrong path using the compound IF+Instr statements (call me the crazy newb...but I thought that was the way to go since I had multiple substrings to look for)?

**3) According to another, my "function declaration [does] not specify that wanted an argument passed to the function...". I'm staring at Vine right now as I speak...I get passing a function...but how do I specify that in VB???

(Oh yeah, anybody know of any good books for VBA work in Access--other then Vine...I'd really be interested in getting further with my VBA training, since .NET seems to be the only thing offered around here, and most of my (work) software versions doesn't recognize .NET yet).

 

[SpentGeezer]

Wow, that is a lot of hard work. Would be easier just to go into the table and use find/replace. Good Luck!

 

[MisB7]

I wish I could just use 'ole 'find and replace', but, unfortunately I have 21 tables (and growing) with over 1,000 records a piece, and at least 100-200 string abbreviations (which are basically the same throughout each table) to change in each table.

Right now I'm looking for a Custom Find-&-Replace function OR a way to use Replace w/ Lookup Tables (i.e. SQL...need the lookup table because I can't input more then 19 REPLACE statements in one query...and as I said, I have 100's of strings to replace).

 

[WayneRyan]

M,

You can make a Table "tblConversions" containing:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Select * From tblConversions")
While Not rst.EOF And Not rst.BOF
   '
   ' Create a sql like: "Update SomeTable
   '                          "Set       Fieldx = Replace(Fieldx, "OldString", "NewString")
   '
   CurrentDb.Execute "Update " & rst!TableName & " " & _
                "Set " & rst!FieldName & " = " & "Replace(" & rst!FieldName & ", " & _
                                                    Chr(34) & rst!OldString & Chr(34) & ", " & _
                                                    Chr(34) & rst!OldString & Chr(34) & ")"
   rst.MoveNext
   Wend

If you have spaces in your table/field name you'll have to "shoehorn" in the "[some field]" around them.

Wayne

 

[MisB7]

Hi Wayne,

Thanks for your feedback...but, I'm not sure I follow you...is this a function?

I'm having considerable difficulties passing (any) of the functions I script in VB into a SQL query. If you can, could you show me how to set this up so I can make a test run of it with my data (enclosing sample w/Modules and other trials)?