# Help Combining IIf Statements

• ### Question

• Hello -

So I have been boggling my mind trying to figure out how to combine the 4 statements below together. Each one works great independently, but I cannot seem to find a way to combine into one statement. I know it is probably placement of  parentheses somewhere, or an "&", "And" or an "Or" somewhere; just can't figure out the right combination :-(

Here's what I got....

1. IIf((DLookUp("[Gas]","Property_Information")=True) And (DLookUp("[Cable]","Property_Information")=True),"Water, Sewer, Trash, Gas, Cable"))  ......... OR
2. IIf(DLookUp("[Gas]","Property_Information")=True,"Water, Trash, Sewer, Gas")  .........OR
3. IIf(DLookUp("[Cable]","Property_Information")=True,"Water, Trash, Sewer, Cable")  .........OR
4. "Water, Sewer, Trash" (if none of the above apply)

If anyone would have any ideas I sure would appreciate. Thanks in advance for your time.

Friday, February 16, 2018 11:53 PM

• Here is one combination I have tried. Produces no errors; but also produces no results :-(

=IIf((((DLookUp("[Gas]","Property_Information")=True) And (DLookUp("[Cable]","Property_Information")=True),"Water, Sewer, Trash, Gas, Cable")),IIf((DLookUp("[Gas]","Property_Information")=True) And (DLookUp("[Cable]","Property_Information")=False),"Water, Trash, Sewer, Gas"),IIf((DLookUp("[Cable]","Property_Information")=True) And ((DLookUp("[Gas]","Property_Information")=False),"Water, Sewer, Trash, Cable"),"Water, Sewer, Trash")))

Hi rstreets2,

You could also try something like:

`= "Water, Sewer, Trash" & IIf(DLookup("Gas","Property_Information")=True," ,Gas","") & IIf(DLookup("Cable","Property_Information")=True," ,Cable","")`

Imb,

• Marked as answer by Tuesday, February 20, 2018 5:47 PM
Saturday, February 17, 2018 8:10 AM

### All replies

• Hello again -

Just thought of something...these two lines should replace lines 2 & 3 in my original post....which do work independently....

2. =IIf((DLookUp("[Gas]","Property_Information")=True) And (DLookUp("[Cable]","Property_Information")=False),"Water, Trash, Sewer, Gas")

3. =IIf((DLookUp("[Cable]","Property_Information")=True) And (DLookUp("[Gas]","Property_Information")=False),"Water, Trash, Sewer, Cable")

Saturday, February 17, 2018 12:41 AM
• Here is one combination I have tried. Produces no errors; but also produces no results :-(

=IIf((((DLookUp("[Gas]","Property_Information")=True) And (DLookUp("[Cable]","Property_Information")=True),"Water, Sewer, Trash, Gas, Cable")),IIf((DLookUp("[Gas]","Property_Information")=True) And (DLookUp("[Cable]","Property_Information")=False),"Water, Trash, Sewer, Gas"),IIf((DLookUp("[Cable]","Property_Information")=True) And ((DLookUp("[Gas]","Property_Information")=False),"Water, Sewer, Trash, Cable"),"Water, Sewer, Trash")))

Saturday, February 17, 2018 1:00 AM
• Here is one combination I have tried. Produces no errors; but also produces no results :-(

=IIf((((DLookUp("[Gas]","Property_Information")=True) And (DLookUp("[Cable]","Property_Information")=True),"Water, Sewer, Trash, Gas, Cable")),IIf((DLookUp("[Gas]","Property_Information")=True) And (DLookUp("[Cable]","Property_Information")=False),"Water, Trash, Sewer, Gas"),IIf((DLookUp("[Cable]","Property_Information")=True) And ((DLookUp("[Gas]","Property_Information")=False),"Water, Sewer, Trash, Cable"),"Water, Sewer, Trash")))

Hi rstreets2,

You could also try something like:

`= "Water, Sewer, Trash" & IIf(DLookup("Gas","Property_Information")=True," ,Gas","") & IIf(DLookup("Cable","Property_Information")=True," ,Cable","")`

Imb,

• Marked as answer by Tuesday, February 20, 2018 5:47 PM
Saturday, February 17, 2018 8:10 AM
• The root of the problem is that the Property_Information table is incorrectly designed.  By having multiple columns, one for each service, data is being 'encoded as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

The correct model would be to have a Properties table, a Services table and a PropertyServices table which resolves the many-to-many relationship type into two one-to-many relationship types as follows:

PropertyServices
....PropertyID   (FK)
....ServiceID    (FK)

The primary key of the table is a composite one of the two foreign key columns.

To return a list of the services per property a VBA concatenation function can then be used.  You'll find an example which uses the highly efficient GetString method of the ADO recordset object in Concat.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.