Solved

Nested IF loop in Control Source of Form

Posted on 2003-11-20
11
463 Views
Last Modified: 2008-03-04
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
Comment
Question by:Aapkaahmad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 5

Accepted Solution

by:
morpheus30 earned 175 total points
ID: 9789985
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
 
LVL 54

Expert Comment

by:nico5038
ID: 9790286
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
 
LVL 28

Expert Comment

by:TextReport
ID: 9790680
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 28

Expert Comment

by:TextReport
ID: 9790699
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
 

Author Comment

by:Aapkaahmad
ID: 9790823

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
 
LVL 54

Expert Comment

by:nico5038
ID: 9790897
=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
 
LVL 54

Expert Comment

by:nico5038
ID: 9790904
Better this time Andrew :-)

Nic;o)
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9790953
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
 
LVL 54

Expert Comment

by:nico5038
ID: 9791159
Just offered room to have different messages Andrew :-)

Nic;o)
0
 

Author Comment

by:Aapkaahmad
ID: 9791469

morpheus30,

Its weird but your original function was correct!

Enjoy the points. Thanks guys for helping out.
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9791493
I guess sometimes it pays to just state the obvious, huh???

Thanks Aapkaahmad!!!
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question