IIF Query

[ssworthi]

Happy Monday everyone!

I am trying to define two types of data in one query; maybe that's why it's not quite working. I have account code and task code which I am trying to define the earn code for payroll purposes: This is my IIF statement if you could help me, it would be so very much apprectiated:

EARNC: IIf([Acct Code]=5104100,"REG",IIf([Acct Code]=5112100,"VAH",IIf([Task]='00059',"BPR")))

Acct Code is number format and task is text. Thanks for any help; I really appreciate it!

 

[spikepl]

"not quite working" means what exactly?

 

[ssworthi]

The first two arguements are pertaining to the account code: Iff([account code] =5104100,"REG", IIf [Account Code]=5112100,"VAH")) Works fine. Now I am adding the following the result is that the earn code reflects REG instead of BPR.

IFF([account code] =5104100,"REG", IIf [Account Code]=5112100,"VAH",IIF([Task]='00059',"BPR"))) I put the ' ' because the field is text.

 

[spikepl]

IIF([Task]="00059","BPR")

 

[ssworthi]

This is what I used VAHEC: IIf([Acct Code]=5104100,"REG",IIf([Acct Code]=5112100,"VAH",IIf([Task]="00059","BPR")))

and am still getting the result of REG for the task 00059;maybe should I quotes around the Acct Code i.e., ="5104100","REG". Thanks again for your help, I really, really appreiciate the help.

 

[spikepl]

I am lost.

Show the data, in all the tablefields shown in your formula, the result you obtain, and the result you desire.

 

[jdraw]

This is what I used VAHEC: IIf([Acct Code]=5104100,"REG",IIf([Acct Code]=5112100,"VAH",IIf([Task]="00059","BPR")))

and am still getting the result of REG for the task 00059;maybe should I quotes around the Acct Code i.e., ="5104100","REG". Thanks again for your help, I really, really appreiciate the help.

I don't think quotes have anything to do with the issue.

When you say that Task ="00059" is still getting "REG", it may be that you are dealing with the record where

[Acct Code]=5104100

If the Acct Code is 5104100, I think you'll get "REG" and that's it for the iif for that record.

Check the Acct code on the record with Task ="00059".

And as spikepl has said, show us the data.

 

[ssworthi]

IIF Qry:VAHEC: IIf([Acct Code]=5104100,"REG",IIf([Acct Code]=5112100,"VAH",IIf([Task]="0000059","BPR")))
Result
qry_sernaPay Period EndIDNameHourly RtDtDeptIDVAHECAcct CodeTaskHoursDiffAmount Owed5/14/2011021995Serna-Aguilera,Maximo12.945/9/20112080251REG5104100000019.5$1.81$17.205/14/2011021995Serna-Aguilera,Maximo12.945/10/20112080251REG5104100000019.5$1.81$17.205/14/2011021995Serna-Aguilera,Maximo12.945/11/20112080251REG5104100000019$1.81$16.295/14/2011021995Serna-Aguilera,Maximo12.945/11/20112080715REG5104100000591$1.81$1.815/14/2011021995Serna-Aguilera,Maximo12.945/12/20112080236REG5104100000019.5$1.81$17.205/14/2011021995Serna-Aguilera,Maximo12.945/13/20112080271REG5104100000019.5$1.81$17.205/21/2011021995Serna-Aguilera,Maximo12.945/16/20112080271REG5104100000019$1.81$16.295/21/2011021995Serna-Aguilera,Maximo12.945/17/20112080723REG5104100000159$1.81$16.295/21/2011021995Serna-Aguilera,Maximo12.945/18/20112080251REG5104100000019$1.81$16.295/21/2011021995Serna-Aguilera,Maximo12.945/19/20112080251REG51041000000110$1.81$18.105/21/2011021995Serna-Aguilera,Maximo12.945/20/20112080251REG51041000000116.5$1.81$29.875/21/2011021995Serna-Aguilera,Maximo12.945/20/20112080740REG5104100000153.5$1.81$6.34

 

[jdraw]

So where exactly do you see Task= "0000059"?

If I recall correctly, once the IIF processes true , it then processes the next record.
IIF does not move on to process the next embedded IIF.

 

[ssworthi]

qry_sernaPay Period EndIDNameHourly RtDtDeptIDEarn CodeAcct CodeTask5/14/2011021995Serna-Aguilera,Maximo12.945/9/20112080251REG5104100000015/14/2011021995Serna-Aguilera,Maximo12.945/10/20112080251REG5104100000015/14/2011021995Serna-Aguilera,Maximo12.945/11/20112080251REG5104100000015/14/2011021995Serna-Aguilera,Maximo12.945/11/20112080715REG5104100000595/14/2011021995Serna-Aguilera,Maximo12.945/12/20112080236REG5104100000015/14/2011021995Serna-Aguilera,Maximo12.945/13/20112080271REG5104100000015/21/2011021995Serna-Aguilera,Maximo12.945/16/20112080271REG5104100000015/21/2011021995Serna-Aguilera,Maximo12.945/17/20112080723REG5104100000155/21/2011021995Serna-Aguilera,Maximo12.945/18/20112080251REG5104100000015/21/2011021995Serna-Aguilera,Maximo12.945/19/20112080251REG5104100000015/21/2011021995Serna-Aguilera,Maximo12.945/20/20112080251REG5104100000015/21/2011021995Serna-Aguilera,Maximo12.945/20/20112080740REG510410000015

 

[jdraw]

As per my previous post:

I believe the multiple embedded iifs are not being triggered in the manner you think.

In the attached jpg, the acct code is 5104100.

So the iif begins processing

IIf([Acct Code]=5104100,"REG",IIf([Acct Code]=5112100,"VAH",IIf([Task]="0000059","BPR")))

The Bold condition is True, so he does the true part and sets EARN to REG

and because he(Access) has processed the True condition, he is finished with that record. He doesn't care, he doesn't even look at
IIf([Task]="0000059","BPR"

IIF is true/ false. He found True, processed it and now is done.
http://www.techonthenet.com/access/functions/advanced/iif.php


In my view, you will have to devise some other logic, or vba, to ensure both conditions are acted on.

 

[ssworthi]

I was actually thinking that as well and thought maybe two separate queries and then join the queries to give all the results I am looking for; sound about right?

 

[Rabbie]

IIF actually evaluates both the TRUE bit and the FALSE bit every time which can cause funnies with Nulls and divisions by Zero. However with nested IIF like the one in the OP since the first condition is TRUE it will use the True value ("REG") regardless of the other IIF

If you want to test two values then you would need to use something like

IIF(a="1234" and b="456","ValueTrue","ValueFalse")

 

[ssworthi]

Thank you all for your help. So to overcome this being two different fields could I:
IIf([task]="00059", "BPR, iif([Account_Code]=5112100,"VAH","REG")))

 

[Rabbie]

Thank you all for your help. So to overcome this being two different fields could I:
IIf([task]="00059", "BPR, iif([Account_Code]=5112100,"VAH","REG")))

If you want BPR returned for every record where Task = 00059 then that will work. If Task not = 00059 then VAH will be returned if Account code = 51112100 otherwise REG