databasedev.co.uk - database solutions and downloads for microsoft access

Click here to view Access 2007 Pure SQL

Access 2007 Pure SQL

Conditional concatenation with the iif() function

Access 2007 Pure SQL - Click here to buy the book.

Concatenating supplier fields using state as a condition

Sometimes, we might not want to apply the same concatenation rules for all of the records in the query. For example, we might want to create a certain label or letter for suppliers in NY and a different label or letter for everyone else, all in the same query.

We can absolutely do this by using conditional statements and the iif() function in particular.

However, someone might ask why we need to do this and not use simple criteria to do the same thing.

First, in databases, we can achieve the same task in 20 different ways. There is always another less time-consuming and more effective way.

Second, in this case, if we use a simple criterion such as (state = 'NY'), we only get the suppliers in NY. What we want is to send two different letters - one formatted for the state of NY and one for everyone else!

Using the iif(condition, true, false) function below, we tell the database to concatenate the companyname, contacttitle, and contactname fields if the state is NY. If it is not, we tell the database to concatenate only the contacttitle and contactname fields. This way, we can create two different sets of labels in the same query.

Of course, we can use our imagination and modify the SQL statement below to use it for letters with plain text or any other concatenation task we need.

 

Code:

SELECT IIf(([State]='NY'),[Companyname]+' '+[ContactTitle]+' '+[Contactname], 
[ContactTitle]+' '+[Contactname]) AS MailTo
FROM Suppliers

Result:

About the Author

Pindar is the author of Access 2007 Pure SQL

Pindar's exciting relationship with databases started with DBase III back in 1991, continuing with all versions of Access since early 1993, and working with MS SQL Server, MySQL, Oracle, and IBM DB2 for a number of years. From then on, he is still in love with all of them. After almost twenty years, he still works with data, information processing, integration, and dissemination.

Pindar is currently a Clinical Assistant Professor of Information Systems at the business school of Rensselaer Polytechnic Institute in Troy, New York where he is teaching databases for the last ten years. Pindar also completed and collaborated on a myriad of database projects for organizations or in collaborative efforts between the University and various corporations.

Pindar’s interests in information science, transactional systems, and analytics focus on creating more efficient and flexible organizations. The idea is to accomplish more with fewer resources and in less time leaving a small footprint on the environment. Pindar’s education includes a BS from the American College in Thessaloniki Greece, an MS, MBA, and a PhD in the United States. He received national and international distinctions for his work in the field and faculty awards for his teaching methods.

Nevertheless, the majority of the author’s experience came from participating in a multitude of industry projects. There, everything has to work efficiently, reliably and above all be acceptable by the people of the corporation. Theoretical knowledge, though useful, takes a second place in these cases. A solid application and strong promotion within the organization are the primary success factors. This is the main reason for which theory takes a secondary part in this book and when it does it is only to support a business task.

No matter what the restrictions of time, Pindar will find the time to grow his tomatoes, eggplants, peppers, squash, onions, and in general anything he can find for his vegetable garden.