Update query using If statements

[INeedAccessHelp]

I am currently trying to update a certain field in Table1 with the data in the same field of a matching record (based on primary fields) in Table2 IF that field in Table1 is empty or begins with 9. I thought I had it, but the query is updating the specified field in ALL of the records not just the matching record. I have the two tables linked using Option #2 in the Join Properties. Here is what I have entered in the Update To cell of the query grid under the "MSA Brand Code" field:

IIf([tblUnclassifiedItems]![MSA Brand Code] Is Null Or [tblUnclassifiedItems]![MSA Brand Code] Like '9*',[tblInvalids-Resolved]![MSA Brand Code],[tblUnclassifiedItems]![MSA Brand Code])

Any help would be greatly appreciated.

Thanks
Rob

 

[richardii]

You could try a different approach. I haven't really tailored this to your case - sorry. Use a function instead, and pass the values to it:

Function Change(cField1, cField2)
If mid(cField1,1,1)="9" OR cField1 = "" Then
cField2 = "Whatever"
Else
cField2= "The Other"
End If
'this line causes the Function Change() to return the value of cField
Change=cField2
End Function

You then build this into a Query (which you turn into a make table if desired). The beauty of doing it this way is that you can make a sequence of SELECT queries (and avoid UPDATES). You can then end by making a table.

 

[INeedAccessHelp]

Thanks. MichaelRed helped me out with this one. All I needed to do was place [tblInvalids-Resolved].[MSA Brand Code] in the Update To cell on the query grid and specify Like "9*" in the Criteria cell. It seems to be working fine.