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:
With this (not working...missing an Operator in 'SearchIt'"") SQL statement:
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).
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).