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.

Answer 1

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")

Answer 2

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")))

Answer 3

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,

Answer 4

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.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file includes a GetList function, and illustrates its use by means of a report, using Northwind data as an example.  Allen Brown also provides a concatenation function, using DAO, at:

http://allenbrowne.com/func-concat.html

In my same OneDrive folder, you'll find a file UnencodeColumns.zip which illustrates how an incorrectly designed table of this type can be automatically decomposed into a set of correctly normalized related tables.  The option for Boolean values would be the appropriate model in your case.

---

Ken Sheridan, Stafford, England

Answer 5

And that is the combo I was looking for! Thank you LMB! Works like a charm! Had tried a couple of variations similar to yours, but could not get to work together :-(  Yours works perfectly! Thank you, thank you, thank you!