Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

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([RecBalConfLetter]) And DateDiff("d",Date(),[StartDate])<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.
0
Aapkaahmad
Asked:
Aapkaahmad
  • 4
  • 3
  • 2
  • +1
1 Solution
 
morpheus30Commented:
I don't understand...

It sounds like you want to do this:
=IIf(IsNull([RecBalConfLetter]) And IsNull([BalanceComments]) And DateDiff("d",Date(),[StartDate])<14,"Balance Confirmation Letter Missing","No Alerts")

But you could've figured that out yourself.  Maybe I'm missing something
0
 
nico5038Commented:
Nesting IIF is possible in the True and/or False part like:
=IIF(<condition>,True,False)
nested:
=IIF(<condition>,=IIF(<condition>,True,False),False)
or
=IIF(<condition>,True,=IIF(<condition>,True,False))

Getting the idea ?

Nic;o)

0
 
TextReportCommented:
Nic;o) I see you are up to your old tricks ;-) copying the extra =

=IIF(<condition>,IIF(<condition>,True,False),False)
or
=IIF(<condition>,True,IIF(<condition>,True,False))

Cheers, Andrew
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TextReportCommented:
Nesting IIF() can get very confusing, you can also use the Switch() function

Switch(Condition1, Return1, Condition2, Return2, ...., CondiotionX, ReturnX, True, ReturnIfAllEsleFailed)

Cheers, Andrew
0
 
AapkaahmadAuthor Commented:

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([RecBalConfLetter]) And DateDiff("d",Date(),[StartDate])<14,"Balance Confirmation Letter Missing","No Alerts")

Thanks.
0
 
nico5038Commented:
=IIf(IsNull([RecBalConfLetter]) And DateDiff("d",Date(),[StartDate])<14 AND BalanceComments is null,"Balance Confirmation Letter Missing",IIf(IsNull([RecBalConfLetter]) And DateDiff("d",Date(),[StartDate])<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)
0
 
nico5038Commented:
Better this time Andrew :-)

Nic;o)
0
 
TextReportCommented:
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([RecBalConfLetter]) And DateDiff("d",Date(),[StartDate])<14 AND IsNull(BalanceComments)) or (IsNull([RecBalConfLetter]) And DateDiff("d",Date(),[StartDate])<14),"Balance Confirmation Letter Missing","Balance Confirmation Letter Missing","No Alerts")

Cheers, Andrew

0
 
nico5038Commented:
Just offered room to have different messages Andrew :-)

Nic;o)
0
 
AapkaahmadAuthor Commented:

morpheus30,

Its weird but your original function was correct!

Enjoy the points. Thanks guys for helping out.
0
 
morpheus30Commented:
I guess sometimes it pays to just state the obvious, huh???

Thanks Aapkaahmad!!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now