Aapkaahmad
asked on
Nested IF loop in Control Source of Form
Suppose that I have four textboxes on a form (Balance, BalanceComments, StartDate, & Alert) Currently what I have is this:
IF Balance is null AND the difference between today's date and StartDate is less than 14 days, THEN the Alert box will display "Balance Confirmation Letter Missing". Here is the function that I am putting in the control source of the Alert box in the function and it works perfectly!
=IIf(IsNull([RecBalConfLet ter]) And DateDiff("d",Date(),[Start Date])<14, "Balance Confirmation Letter Missing","No Alerts")
Now what I want is IF Balance is null AND BalanceComments is null AND the difference between today's date and StartDate is less than 14 days, THEN the Alert box will display "Balance Confirmation Letter Missing". Basically the difference is that now I also want to check the BalanceComments field to see if it is null or not. How do I edit my current (above) function to achieve this?
Thanks in advance.
IF Balance is null AND the difference between today's date and StartDate is less than 14 days, THEN the Alert box will display "Balance Confirmation Letter Missing". Here is the function that I am putting in the control source of the Alert box in the function and it works perfectly!
=IIf(IsNull([RecBalConfLet
Now what I want is IF Balance is null AND BalanceComments is null AND the difference between today's date and StartDate is less than 14 days, THEN the Alert box will display "Balance Confirmation Letter Missing". Basically the difference is that now I also want to check the BalanceComments field to see if it is null or not. How do I edit my current (above) function to achieve this?
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nic;o) I see you are up to your old tricks ;-) copying the extra =
=IIF(<condition>,IIF(<cond ition>,Tru e,False),F alse)
or
=IIF(<condition>,True,IIF( <condition >,True,Fal se))
Cheers, Andrew
=IIF(<condition>,IIF(<cond
or
=IIF(<condition>,True,IIF(
Cheers, Andrew
Nesting IIF() can get very confusing, you can also use the Switch() function
Switch(Condition1, Return1, Condition2, Return2, ...., CondiotionX, ReturnX, True, ReturnIfAllEsleFailed)
Cheers, Andrew
Switch(Condition1, Return1, Condition2, Return2, ...., CondiotionX, ReturnX, True, ReturnIfAllEsleFailed)
Cheers, Andrew
ASKER
morpheus30, I had already tried what you are suggestion and it did not work.
nico5038, TextReport....
Can you use your examples to try to make the function that I am trying to make?
=IIf(IsNull([RecBalConfLet
Thanks.
=IIf(IsNull([RecBalConfLet ter]) And DateDiff("d",Date(),[Start Date])<14 AND BalanceComments is null,"Balance Confirmation Letter Missing",IIf(IsNull([RecBa lConfLette r]) And DateDiff("d",Date(),[Start Date])<14, "Balance Confirmation Letter Missing","No Alerts"))
It will however be a bit "strange" that both tested conditions give the same error message when true....
Nic;o)
It will however be a bit "strange" that both tested conditions give the same error message when true....
Nic;o)
Better this time Andrew :-)
Nic;o)
Nic;o)
Nic;o) Not Bad but your mixing your IsNull and Is Null options if both true coditions return the same string then I would do it in 1 IIF()
=IIf((IsNull([RecBalConfLe tter]) And DateDiff("d",Date(),[Start Date])<14 AND IsNull(BalanceComments)) or (IsNull([RecBalConfLetter] ) And DateDiff("d",Date(),[Start Date])<14) ,"Balance Confirmation Letter Missing","Balance Confirmation Letter Missing","No Alerts")
Cheers, Andrew
=IIf((IsNull([RecBalConfLe
Cheers, Andrew
Just offered room to have different messages Andrew :-)
Nic;o)
Nic;o)
ASKER
morpheus30,
Its weird but your original function was correct!
Enjoy the points. Thanks guys for helping out.
I guess sometimes it pays to just state the obvious, huh???
Thanks Aapkaahmad!!!
Thanks Aapkaahmad!!!
=IIF(<condition>,True,Fals
nested:
=IIF(<condition>,=IIF(<con
or
=IIF(<condition>,True,=IIF
Getting the idea ?
Nic;o)