IIf function not working

[Ranjit kurian]

Can anyone guide, why the below iif function is not working.

DATE: IIf(([Report List]!Frequency="Monthly" And [Report List]!Day="Week1"
And [Diff]<="6"),
IIf(([Report List]!Frequency="Monthly" And [Report List]!Day="Week2" And
[Diff]<="13"),
IIf(([Report List]!Frequency="Monthly" And [Report List]!Day="Week3" And
[Diff]<="19"),IIf(([Report List]!Frequency="Monthly" And [Report
List]!Day="Week4" And [Diff]<="31"),Date()))))

 

[Rob Parker]

What exactly do you mean by "is not working"?

It's a multi-nested Iif function - which part is failing? One obvious
problem is that the inner-most Iif statement does not have a raturn value
for the false condition.

This isn't the sort of problem that others can help with; you'd be better
trying each of your Iif statements separately on some test data (working
outwards from the inner-most condition), and seeing which bit returns
incorrect results. Maybe it's working perfectly well (apart from there
being no final false condition), but there are no records that match the
overall condition.

HTH,

Rob

 

[KARL DEWEY]

Of all those IFs you only have a single result and it can never be true.
You are testing the first and if true then you test again and it can not be
true but there is no false answer.
What do you want the answer to be if this test true -- [Report
List]!Frequency="Monthly" And [Report List]!Day="Week1" And [Diff]<="6" ??

You have 4 test but only one result where there should be 5 - 4 true & 1
false.

 

[Ranjit kurian]

actually i hv three column(Frequency, Day, Diff)

if Frequency column is Monthly and if Day column is Week1(week1, week2,
week3.....), and if Diff is less than or equal 6, less than or equal 13,less
than or equal 19, less than or equal 31 then the answer should be today's
date if answer is false then it should be blank...

actually i have a list for number like
1 to 6 week1
7 to 13 week2
14 to 19 week3
19 to 31 week4

Of all those IFs you only have a single result and it can never be true.
You are testing the first and if true then you test again and it can not be
true but there is no false answer.
What do you want the answer to be if this test true -- [Report
List]!Frequency="Monthly" And [Report List]!Day="Week1" And [Diff]<="6" ??

You have 4 test but only one result where there should be 5 - 4 true & 1
false.

--
KARL DEWEY
Build a little - Test a little

Can anyone guide, why the below iif function is not working.

DATE: IIf(([Report List]!Frequency="Monthly" And [Report List]!Day="Week1"
And [Diff]<="6"),
IIf(([Report List]!Frequency="Monthly" And [Report List]!Day="Week2" And
[Diff]<="13"),
IIf(([Report List]!Frequency="Monthly" And [Report List]!Day="Week3" And
[Diff]<="19"),IIf(([Report List]!Frequency="Monthly" And [Report
List]!Day="Week4" And [Diff]<="31"),Date()))))

 

[KARL DEWEY]

Try this --
IIF([Report List].[Frequency]="Monthly" And (([Day] = "week1" And [Diff]
Between 1 And 6) Or ([Day] = "week2" And [Diff] Between 7 And 13) Or ([Day] =
"week3" And [Diff] Between 14 And 19) Or ([Day] = "week4" And [Diff] Between
20 And 31)), Date(), Null)



--
KARL DEWEY
Build a little - Test a little

actually i hv three column(Frequency, Day, Diff)

if Frequency column is Monthly and if Day column is Week1(week1, week2,
week3.....), and if Diff is less than or equal 6, less than or equal 13,less
than or equal 19, less than or equal 31 then the answer should be today's
date if answer is false then it should be blank...

actually i have a list for number like
1 to 6 week1
7 to 13 week2
14 to 19 week3
19 to 31 week4

Of all those IFs you only have a single result and it can never be true.
You are testing the first and if true then you test again and it can not be
true but there is no false answer.
What do you want the answer to be if this test true -- [Report
List]!Frequency="Monthly" And [Report List]!Day="Week1" And [Diff]<="6" ??

You have 4 test but only one result where there should be 5 - 4 true & 1
false.

--
KARL DEWEY
Build a little - Test a little

Can anyone guide, why the below iif function is not working.

DATE: IIf(([Report List]!Frequency="Monthly" And [Report List]!Day="Week1"
And [Diff]<="6"),
IIf(([Report List]!Frequency="Monthly" And [Report List]!Day="Week2" And
[Diff]<="13"),
IIf(([Report List]!Frequency="Monthly" And [Report List]!Day="Week3" And
[Diff]<="19"),IIf(([Report List]!Frequency="Monthly" And [Report
List]!Day="Week4" And [Diff]<="31"),Date()))))