Choose between 2 columns based on another field in query (1 Viewer)

Amms

New member
Local time
Today, 20:56
Joined
Nov 10, 2019
Messages
2
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.
My objective:
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.

Thank you
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:56
Joined
Aug 30, 2003
Messages
36,118
Try this shot in the dark in a new field in the query:

CurrentStatus: IIf(DateEffective >= date(), EffectiveStatus, PendingStatus)
 

Amms

New member
Local time
Today, 20:56
Joined
Nov 10, 2019
Messages
2
Try this shot in the dark in a new field in the query:

CurrentStatus: IIf(DateEffective >= date(), EffectiveStatus, PendingStatus)

Thank u. Brain fart. I had not even tried including tblB in the query. Simple solution.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:56
Joined
Oct 29, 2018
Messages
21,358
Hi Amms. Welcome to AWF!
 

Users who are viewing this thread

Top Bottom