Update fields with complicated algorithim: SQL or VB?

[luckycharms]

Hi Folks,

I need to update a foreign key column in a table. The data in my two tables is somewhat messy, and hence my query needs to be somewhat complicated. I'm not sure if it's better done in SQL or VB, and in either case, how. Here's what I need to do in psedo-code:

UPDATE table_1 t1, table_2 t2
    IF t1.col1 NOT = "string1" or "string2" THEN
        SET t1.t2ID = t2.ID WHERE
            t1.match1 = t2.match1
    ELSE
        SET t1.t2ID = t2.ID WHERE
            t1.match2 = t2.match2

I probably would prefer to do this via VB, as it might afford more flexibility into the future as I need to tweak things. But, my ears are wide open. Be it via VB or SQL, how might I do this?

Thanks a ton!

[June7]

I don't quite understand the WHERE clauses. Don't know if that can be dynamic in Access query object.

UPDATE table_1 As t1 INNER JOIN table_2 As t2 ON t1.ID = t2.ID SET t1.t2ID = t2.ID WHERE IIf(t1.col1 <> "string1" or t1.col1 <> "string2", t1.match1=t2.match1, t1.match2=t2.match2)

What has to be 'tweaked'? If you want the string1 and string2 to be dynamic, they can be input parameters in the query. VBA code could run the sql:

CurrentDb.Execute "UPDATE ..."

[luckycharms]

Thanks for the reply.

Here's what I've ended up implementing in VB. I imagine there are better ways of going about it, but this works for me. Thanks for your help!

Sub Link_table1()

CurrentDb.Execute "UPDATE table1 AS t1 SET t1.t2ID=NULL"

' Join on col1 and match1 when match1 meets a criterion

CurrentDb.Execute "UPDATE table1 AS t1, Table2 AS t2 SET " _
& "t1.t2ID = t2.ID WHERE t1.col1 = t2.col1 AND t1.match1 = t2.match1 AND t1.match1 = ""string1"""

' Join on col1 and match2 when match1 doesn't meet the criterion

CurrentDb.Execute "UPDATE table1 AS t1, Table2 AS t2 SET " _
& "t1.t2ID = t2.ID WHERE t1.col1 = t2.col1 AND t1.match2 = t2.match2 " _
& "AND t1.match1 <> ""string1"""

End Sub