Replace() a string in a table based another table of values 2

[Cooch]

Hi,

I am trying to use the VBA function Replace() in Access 2000 to replace field values in a table based on values in a field of another.

Example Tables

TblData (approx 40,000 records)
TagNo ProcessData
1PT5 100Kpa
2PCV04 10KPA.G
1JB12 Yellow
1PT07 7psi
5PG03 12kPaa

tblAlias (approx 400 records)
UOM Alias
KPa(a) kPaa
KPa(g) KPA.G
KPa(g) kpag
PSI psi
PSI psig

Basically I am trying to get Access to search the field ProcessData in table tblData for all occurrences of the values in field Alias in table tblAlias. So the code would search for the string "kPaa" in all ProcessData records and replace with Nulls, then search all ProcessData records for KPA.G and replace with Nulls until all occurrences of aliases listed in tblAlias have been replaced by nulls in tblProcessData. Giving me the result below:

Desired Result

TblData
TagNo ProcessData
1PT5 100Kpa
2PCV04 10
1JB12 Yellow
1PT07 7
5PG03 12

I have searched the forums for an answer for over 2 hours for a similar problem to no avail. Any help would be greatly appreciated. I am definitely not a VBA guru but know my way around access pretty well.

 

[RickSpr]

You'd be much better off just running an update query:
UPDATE tblData SET ProcessData = Null
WHERE ProcessData IN (SELECT Alias FROM tblAlias)

That will do the whole job for you with a single statement. You could even do it without VBA code. And it will run much faster.

If you need to do this in a procedure, just use DoCmd.RunSQL to execute it. Rick Sprague

 

[Cooch]

Thanks Rick,

The main reason I was going to use a replace function was because you can't use wildcards in an IN statement. If I have been ill informed, please let me know because I would prefer not to have to use code if I dont have to.

Regards,

Cooch

 

[RickSpr]

But why would you need a wildcard? The statement will work just as I gave it. The "SELECT Alias FROM tblAlias" part returns the entire list of aliases, and the UPDATE statement will set each ProcessData to Null if it matches any of the aliases in the list. Rick Sprague

 

[Cooch]

My apologies for not being clearer. The problem I have is where the Alias from tblAlias is contained within the string in ProcessData in tblData I want to strip that alias from the string. Using example 2 in tblData (2PCV04), as the string "KPA.G" is in tblAlias (line 2) I want to strip only the "KPA.G" text from that value, giving me a result of 10 (10KPA.G with the KPA.G removed). The example you gave me would ignore this value 10KPA.G as it is not a match. It would however pick up yellow if yellow was in tblAlias.

 

[RickSpr]

I didn't read very carefully; I missed the fact that the aliases are embedded in ProcessData.

Here's a routine that will do it. It uses DAO, so if you're using Access 2000 you'll need to add a reference to the Microsoft DAO 3.6 Object Library.

Public Sub RemoveAliases()
    Dim rstAlias As DAO.Recordset
    Dim rstData As DAO.Recordset
    Dim s As String
    
    Set rstAlias = CurrentDb.OpenRecordset("tblAlias", _
        , dbReadOnly)
    Set rstData = CurrentDb.OpenRecordset("tblData")
    Do Until rstAlias.EOF
        rstData.MoveFirst
        With rstData
            Do Until .EOF
                s = Replace(!ProcessData, _
                    rstAlias!Alias, "")
                If s <> !ProcessData Then
                    .Edit
                    !ProcessData = s
                    .Update
                End If
                .MoveNext
            Loop
        End With
        rstAlias.MoveNext
    Loop
    rstData.Close
    Set rstData = Nothing
    rstAlias.Close
    Set rstAlias = Nothing
End Sub

Rick Sprague

 

[Cooch]

Howdy Rick,

Absolutely fantastic!!! It worked an absolute treat. I cannot thank you enough. Out of curiosity, if I wanted to restrict the action to only where the field UOM matched in both tables would it be possible? I didn't ask this question first up because I honestly did't think it was possible to answer my first query and thought it would cloud the issue.

Example Tables

tblData (approx 40,000 records)
TagNo ProcessData UOM
1PT5 100Kpa KPa(g)
2PCV04 10KPA.G KPa(g)
1JB12 Yellow Colour
1PT07 7psig PSI
5PG03 12kPaa kPa(a)

tblAlias (approx 400 records)
UOM Alias
KPa(a) kPaa
KPa(g) KPA.G
KPa(g) kpag
PSI psi
PSI psig

I need the same action to occur as before but only where the UOM matches in both recordsets. In the examples above, looking at line 4 of tblData (1PT07), because it has a UOM of PSI, the code would only look at replacing the Alias with a matching UOM (eg. Psi and psig). It would therefore not look to replace the string kPaa, KPA.G, kpag, etc because they have a different UOM in table tblAlias.

This is a nice to have, not essential so if you are busy I understand. I am booking myself on a VBA course as you have shown me that it can make Access so much more powerful. Thanks again for your help, if you are ever in Perth in Oz, I owe you a few beers.

Best Regards,

Cooch
"It's easy to make a buck. It's a lot tougher to make a difference. "

 

[RickSpr]

Easy! Just replace:
If s <> !ProcessData Then
with:
If (!UOM <> rstAlias!UOM) And (s <> !ProcessData) Then

Actually, if I had known you wanted to match on the UOM in the first place I would have written it much more efficiently, only looking up Aliases for the right UOM, but this will work. If you need the more efficient code, I'll provide it, but I don't have time right now.

I'm glad you took this as a convincing reason to learn VBA. You're right--Access is very powerful when you use code. (It's an excellent tool without it, too, but there's almost nothing you can't do with code.) Rick Sprague

 

[Cooch]

G'day Rick,

Should the statement read:

If (!UOM = rstAlias!UOM) And (s <> !ProcessData) Then

instead of:

If (!UOM <> rstAlias!UOM) And (s <> !ProcessData) Then

I am getting the error

Run-time error '3265':
Item not found in this collection.

Don't tell me, I am going to try and work it out myself (wish me luck!)

Once again, thanks so much for your help. Hopefully in a couple of months I may be able to help someone else in this forum.

Kind regards,

Lorne

 

[RickSpr]

Looks like I got sloppy again on the &quot;<>&quot; vs. &quot;=&quot;.

Ok, I won't give it to you outright. But here's a hint: &quot;!&quot; is the collection search operator. What's on the left is the collection to be searched, and what's on the right is the index value of the item to be found.

!UOM and !ProcessData have nothing to the left, because they are within the With statement; the object named on the With statement (rstAlias) then provides the implicit collection. Since rstAlias is not itself a collection, VBA refers to its default property, Fields, which is a collection. So an alternate reference for !UOM is rstAlias.Fields!UOM.

The error message is telling you that one of the field names was not found in the Fields collection of one of the recordsets. Rick Sprague

 

[Cooch]

Finally sussed it out! I had spelt !ProcessData wrong, works perfectly now. Thanks for your help on this one Rick, much appreciated.

Cheers,

Cooch