Nested IIF Problem or other options.

[Trisha]

Well at least i think that is what i am trying to achieve
my initial IIF statement is working fine ans i have put it in bold down below but i am looking for a way to implement another condition as if i was using an "Else If " or something that will look to another row and if mobilised = "NO" i wish to just enter the total from call handling. :banghead:



(E) Call Handling: IIf(IsNull([(E) Call Taking]),[Call Handling],[(E) Call Taking]) And IIf([Mobilised])= "NO",[Call Handling],[(E) Call Taking])

 

[plog]

I vote move this thing to a function in a module. It's getting too complex for a single line. Then you won't have to decipher which comma goes with which clause. You simply write your code on however many lines it takes.

 

[Brianwarnock]

Are you saying that Mobilised ="no" takes precedence, if so

(E) Call Handling: IIf([Mobilised])= "NO",[Call Handling],IIf(IsNull([(E) Call Taking]),[Call Handling],[(E) Call Taking]))

Brian

 

[MSAccessRookie]

Well at least i think that is what i am trying to achieve
my initial IIF statement is working fine ans i have put it in bold down below but i am looking for a way to implement another condition as if i was using an "Else If " or something that will look to another row and if mobilised = "NO" i wish to just enter the total from call handling. :banghead:



(E) Call Handling: IIf(IsNull([(E) Call Taking]),[Call Handling],[(E) Call Taking]) And IIf([Mobilised])= "NO",[Call Handling],[(E) Call Taking])

I reformatted the QUery and used CODE Tags to display it, and I think that I have located an extra ")". You will need to determine what it is for.

[COLOR=black][SIZE=3][FONT=Times New Roman][B]IIf[SIZE=5][COLOR=lime]([/COLOR][/SIZE]IsNull[SIZE=5][COLOR=darkorchid]([/COLOR][/SIZE][(E) Call Taking][SIZE=5][COLOR=darkorchid])[/COLOR][/SIZE],[/B][/FONT][/SIZE][/COLOR][COLOR=black][SIZE=3][FONT=Times New Roman][B] [Call Handling],[/B][/FONT][/SIZE][/COLOR][SIZE=3][FONT=Times New Roman][B] [(E) Call Taking][SIZE=5][COLOR=lime])[/COLOR][/SIZE] [/B][/FONT][/SIZE]
[COLOR=black][SIZE=3][FONT=Times New Roman][B]And [/B][/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman][B]IIf[SIZE=5][COLOR=lime]([/COLOR][/SIZE][Mobilised][SIZE=5][COLOR=red])[/COLOR][/SIZE]= "NO",[/B][/FONT][/SIZE][/COLOR][COLOR=black][SIZE=3][FONT=Times New Roman][B] [Call Handling],[/B][/FONT][/SIZE][/COLOR][COLOR=black][FONT=Times New Roman][B] [(E) Call Taking][SIZE=5][COLOR=lime])[/COLOR][/SIZE][/B][/FONT][/COLOR]

 

[Trisha]

Well i have tried
IIf(IsNull([(E) Call Taking]), [Call Handling], [(E) Call Taking]) And IIf([Mobilised]= "NO", [Call Handling], [(E) Call Taking]) and it is returning a value of 00:00:00 i have also put in where Mobilised ="no" takes precedence and that is not working for me either
and Plog i would try the route of function in a module but i would not know where to start im a bit of a beginner/intermediate.
basically what i am trying to do in terms of this if statement is if my (E) Call Taking is null i want the value of the call handling to be entered if not enter the value of (E) Call Taking but if mobilised = NO i want the value of call handling to be entered,
i would have thought a nested if would have sufficed but if a function is the way to go i would only love to learn something new.

 

[pr2-eugin]

So what happens if Mobilised = No and CallTracking is Null? What value will the result be?

 

[Trisha]

pr2-eugin,
if Mobilised = No and Call handling is Null i just want an empty return if that is possible.

 

[pr2-eugin]

Try this..

IIF(Mobilised = "No", Nz(CallHandling, ""), Nz(CallTracking, CallHandling))

 

[Trisha]

So what happens if Mobilised = No and CallTracking is Null? What value will the result be?

pr2-eugin,
if Mobilised = No and Call handling is Null i just want an empty return if that is possible.

 

[Trisha]

Try this..

IIF(Mobilised = "No", Nz(CallHandling, ""), Nz(CallTracking, CallHandling))

Hi Paul
Well i have tried as you suggested as follows
IIf([Mobilised]="No",Nz([Call Handling],""),Nz([(E) Call Taking],[Call Handling]))

But this is returning a weird values for example 6.36574070085771E-04 even though I have put the format to long time, can you tell me what Nz stands for also?

 

[pr2-eugin]

Nz is a simplified If statement. It will check for one Condition IsNull. You can lookup on Access help for detailed explanation, or check out THIS LINK.

That is not a weird result if you wrap them using a Date function it should be fine. Try..

[URL="http://ss64.com/access/cvdate.html"]CVDate[/URL](IIf([Mobilised]="No", [Call Handling], Nz([(E) Call Taking],[Call Handling])))

 

[Trisha]

Nz is a simplified If statement. It will check for one Condition IsNull. You can lookup on Access help for detailed explanation, or check out THIS LINK.

That is not a weird result if you wrap them using a Date function it should be fine. Try..

[URL="http://ss64.com/access/cvdate.html"]CVDate[/URL](IIf([Mobilised]="No", [Call Handling], Nz([(E) Call Taking],[Call Handling])))

Thank you so very much i have learnt a new way of doing an IIF as well as getting this query working thank you again
Trish

 

[pr2-eugin]

Glad to have helped.

Just a small note, if your IIF goes anywhere more than 3 conditions deep, its best to move into a function, to avoid complexity and easier logical understanding. Good Luck.