Solved

Nested IF loop in Control Source of Form

Posted on 2003-11-20
11
460 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
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

831 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