Update with if condition

[drunkenneo]

I have a table

Main

Name Inter Intra Flag
------------------
A BB CC
as DD FF

And Table

Code table:

code Flag
----------


BB 1
CC 0
DD 0
FF 0


All the Flag reference taken from table, I want an update query where it checks if either of Inter and intra from Main table have flag as 1 from code table, it should update the flag in main as 1 else 0.

Could this be possible?

[June7]

Try:

UPDATE Main SET Flag = IIf(IsNull(DLookup("Flag", "Code", "Flag=1 AND ([code]='" & [Inter] & "' OR [code]='" & [Intra] & "'"))), 0, 1)


Actually, saving calculated data is usually a bad idea. The Flag value can just be calculated when need.

SELECT [Name], Inter, Intra, IIf(IsNull(DLookup("Flag", "Code", "Flag=1 AND ([code]='" & [Inter] & "' OR [code]='" & [Intra] & "'"))), 0, 1) AS Flag FROM Main;