Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Nested IF loop in Control Source of Form

Posted on 2003-11-20
11
Medium Priority
?
466 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 700 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

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

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

718 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