Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-09-2019, 08:15 PM   #1
Amms
Newly Registered User
 
Join Date: Nov 2019
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Amms is on a distinguished road
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.
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

Amms is offline   Reply With Quote
Old 11-09-2019, 09:13 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,263
Thanks: 13
Thanked 4,110 Times in 4,042 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Choose between 2 columns based on another field in query

Try this shot in the dark in a new field in the query:

CurrentStatus: IIf(DateEffective >= date(), EffectiveStatus, PendingStatus)
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Amms (11-10-2019)
Old 11-10-2019, 02:12 AM   #3
Amms
Newly Registered User
 
Join Date: Nov 2019
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Amms is on a distinguished road
Re: Choose between 2 columns based on another field in query

Quote:
Originally Posted by pbaldy View Post
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.

Amms is offline   Reply With Quote
Old 11-10-2019, 06:44 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Choose between 2 columns based on another field in query

Hi Amms. Welcome to AWF!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-10-2019, 09:13 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,263
Thanks: 13
Thanked 4,110 Times in 4,042 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Choose between 2 columns based on another field in query

Quote:
Originally Posted by Amms View Post
Thank u. Brain fart. I had not even tried including tblB in the query. Simple solution.
No problem.

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Tags
query criteria

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to choose field based on criteria martin28 Queries 2 05-16-2018 08:44 PM
Choose which columns to sum in a Total Row George21 Modules & VBA 6 02-28-2017 11:03 PM
ComboBox to Choose which field in a query to write the criteria Ahmeds Queries 1 04-07-2016 02:45 AM
being able to choose field in update query rrtpeds Queries 2 05-24-2008 11:48 AM




All times are GMT -8. The time now is 08:03 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World