Choose between 2 columns based on another field in query
tblA with relative fields DateEffective and ActionID(FK)
tblB is a reference table with relative fields ActionID(PK), PendingStatus, EffectiveStatus
Ive created a query which includes DateEffective and ActionID from tblA, and a calculated field called CurrentStatus.
if DateEffective >= date() then CurrentStatus= EffectiveStatus where query.ActionID = tblB.ActionID
Else CurrentStatus=PendingStatus where query.ActionID=tblBActionID
Could someone please help me with the query expression syntax. I tried iif(comparing dates, dlookup for pending, dlookup for effective) but it always asks for a parameter value.