Solved

Nested IF loop in Control Source of Form

Posted on 2003-11-20
11
462 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 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