[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

IF AND statement in MS Access

I have a simple(?) Access database used for client info.  One question on a form is "Do you have any cohabitants?"  Also on the form are fields for 6 cohabitants.  On a report, I want a text box to display a sentence based on the entries.

Ex1: Has no cohabitants.
"I, Matt Albrecht am awesome."

Ex2: Has Cohabitants.  Cohab1: Dan
"I, Matt Albrecht, am awesome.  Also awesome is Dan."

Ex3: Has Cohabitants.  Cohab1: Dan Cohab2: Julie
"I, Matt Albrecht, am awesome.  Also awesome are Dan and Julie."

Ex4: Has Cohabitants.  Cohab1: Dan Cohab2: Julie Cohab3: Jason Cohab4: Kendra
"I, Matt Albrecht, am awesome.  Also awesome are Dan, Julie, Jason, and Kendra."

I'm thinking there may be an easier way to do this.  I'm working on it right now.  I think I may have to repeat the IIF statements for each ELSE, which will make this expression ginormous.  We'll see.
What I have so far is below:
=IIf([HasCohab]=0,"I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome.",IIf([Cohab1]<>"",IIf([Cohab2]<>"",IIf([Cohab3])<>"",IIf(Cohab4])<>"",IIf([Cohab5])<>"",IIf([Cohab6]<>"","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome are the other inhabitants of my residence: " & [Cohab1] & ", " & [Cohab2] & ", " & [Cohab3] & ", " & [Cohab4] & ", " & [Cohab5] & ", and " & [Cohab6] & ".","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome are the other inhabitants of my residence: " & [Cohab1] & ", " & [Cohab2] & ", " & [Cohab3] & ", " & [Cohab4] & ", and " & [Cohab5] & ".","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome are the other inhabitants of my residence: " & [Cohab1] & ", " & [Cohab2] & ", " & [Cohab3] & ", and " & [Cohab4] & ".","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome are the other inhabitants of my residence: " & [Cohab1] & ", " & [Cohab2] & ", and " & [Cohab3] & ".","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome are the other inhabitants of my residence: " & [Cohab1] & " and " & [Cohab2] & ".","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome is the other inhabitant of my residence: " & [Cohab1] & "."

Open in new window

0
Ubertam
Asked:
Ubertam
  • 5
  • 3
1 Solution
 
UbertamAuthor Commented:
Here's the updated try.  Now Access is saying I missed an operator or some syntax error.
=IIf([HasCohab]=0,"I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome.",IIf([Cohab1]<>"",IIf([Cohab2]<>"",IIf([Cohab3]<>"",IIf(Cohab4]<>"",IIf([Cohab5]<>"",IIf([Cohab6]<>"","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome are the other inhabitants of my residence: " & [Cohab1] & ", " & [Cohab2] & ", " & [Cohab3] & ", " & [Cohab4] & ", " & [Cohab5] & ", and " & [Cohab6] & ".",IIf([Cohab1]<>"",IIf([Cohab2]<>"",IIf([Cohab3]<>"",IIf(Cohab4]<>"",IIf([Cohab5]<>"","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome are the other inhabitants of my residence: " & [Cohab1] & ", " & [Cohab2] & ", " & [Cohab3] & ", " & [Cohab4] & ", and " & [Cohab5] & ".",IIf([Cohab1]<>"",IIf([Cohab2]<>"",IIf([Cohab3]<>"",IIf(Cohab4]<>"","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome are the other inhabitants of my residence: " & [Cohab1] & ", " & [Cohab2] & ", " & [Cohab3] & ", and " & [Cohab4] & ".",IIf([Cohab1]<>"",IIf([Cohab2]<>"",IIf([Cohab3]<>"","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome are the other inhabitants of my residence: " & [Cohab1] & ", " & [Cohab2] & ", and " & [Cohab3] & ".",IIf([Cohab1]<>"",IIf([Cohab2]<>"","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome are the other inhabitants of my residence: " & [Cohab1] & " and " & [Cohab2] & ".",IIf([Cohab1]<>"","I, " & [FIRSTNAME] & " " & [LASTNAME] & " " & ", am awesome." & "  Also awesome is the other inhabitant of my residence: " & [Cohab1] & ".",""))))))))))))))))))))))

Open in new window

0
 
UbertamAuthor Commented:
I used Notepad++ and I recommend you do the same.  The code is all on one line and thus hard to read in this window.
0
 
Jeffrey CoachmanCommented:
Ubertam,

"Also on the form are fields for 6 cohabitants"

This is not a normalized design.

There should be a "cohabitants" table, linked to the Client table on ClientID.

tblClients
ClientID (Primary Key)
ClientName
...ect

tblCohabitants
CohabitantID (Primary Key)
ClientID (Foreign Key)
CohabitantName
...ect

Do this first, then we can work on the Custom text messages

;-)

JeffCoachman
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

 
UbertamAuthor Commented:
Okay, I have a tab_Cohab with CohabID linked in a relationship with dataID (the Contact table primary key).  I'll change the form so those fields are linked to the new database.  What next?
0
 
UbertamAuthor Commented:
HAHA!  Got it figured out.  Here's the sample I used to test.  I needed to use AND statements with the IIf:

=IIf([HasCohab]=0,"Alone",IIf([Cohab1]<>"" AND [Cohab2]<>"" AND [Cohab3]<>"" AND [Cohab4]<>"" AND [Cohab5]<>"" AND [Cohab6]<>"","Six of us.",IIf([Cohab1]<>"" AND [Cohab2]<>"" AND [Cohab3]<>"" AND [Cohab4]<>"" AND [Cohab5]<>"","Five of us.",IIf([Cohab1]<>"" AND [Cohab2]<>"" AND [Cohab3]<>"" AND [Cohab4]<>"","Four of us.",IIf([Cohab1]<>"" AND [Cohab2]<>"" AND [Cohab3]<>"","Three of us.",IIf([Cohab1]<>"" AND [Cohab2]<>"","Two of us.",IIf([Cohab1]<>"","One of Us.","")))))))

It works like a charm!  Now I just need to adjust the text to include Cohab names!  W00T!!!
0
 
Jeffrey CoachmanCommented:
So are you saying that you "did" or "did not" use my "Normailization" advice?
0
 
UbertamAuthor Commented:
I didn't use the normalization (if you mean having a separate table).  The Cohabs are columns in the main table.  They will only be referenced by that one client record.  The answer to this question was to use AND statements for the condition in the nested IIf statements.  Your advice is probably good for database structure, but I don't have time to learn that right now or mess with it, as we're having our conference Sunday and I'm in a huge rush to get this working.

I appreciate your effort, but unfortunately it didn't help me get my problem solved this time.
0
 
Jeffrey CoachmanCommented:
Fair enough.

;-)

Jeff
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now