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.
SELECT IIf(([State]='NY'),[Companyname]+' '+[ContactTitle]+' '+[Contactname], [ContactTitle]+' '+[Contactname]) AS MailTo FROM Suppliers