CODEWITHSUNDEEP
sqlms-accessif-statementconditional-statements
Hituptony
Hituptony

Reputation: 2860

ACCESS SQL IF() based on previous IF() outcome

so I am building a query, and was curious to know if i have a couple IIF() statements

Example:

SELECT a.RecID, a.DTP_Date as '834Effective', b.[Eff Date], a.[Fed ID] as '834PCP', b.[Provider ID] as '040914PCP', c.PCP_Nbr as 'MEPCP', d.PCP_Nbr as 'PCPHistoryActivePCP',
IIF(a.[Fed ID] = d.PCP_Nbr, 'MatchPCPHistory', 'NOMATCH') as '834_PCP_HistoryMatch',
IIF(a.[Fed ID] = b.[Provider ID] , 'Match0409PCP', 'NOMATCH') as '834_0409Match'

I want another IIF immediately after that says...

IIF(IIF(a.[Fed ID] = d.PCP_Nbr, 'MatchPCPHistory', 'NOMATCH') = 'MatchPCPHistory' 
AND IIF(a.[Fed ID] = b.[Provider ID] , 'Match0409PCP', 'NOMATCH') = 'Match0409PCP', 'UPDATE', 'NOUPDATE')

Would help visually to include this final iif statement to pass it along to my higher ups.

This query didn't work, but maybe I could get it to? I know Access is touchy.

Thanks in advance!

Upvotes: 0

Views: 43

Answers (1)

Racil Hilan
Racil Hilan

Reputation: 25351

You can do that, but would you want to do it in the first place? You already know your original conditions that lead to those values, so use the original conditions instead of the values. Try this:

IIF(a.[Fed ID] = d.PCP_Nbr AND a.[Fed ID] = b.[Provider ID], 'UPDATE', 'NOUPDATE')

Upvotes: 1

Related Questions