Pull correct info based on Mailing Code = Home or Work 1

[tripleblessed]

I'm pulling the correct address based on Mailing Code = "H" or "W" but I need to pull the Title and Company fields if the code = "W". How do I add this to my Select statement? Also, is there a better way to write this?

Here's what I have:
SELECT LSPAAalumni.[Salutation], LSPAAalumni.[First Name], LSPAAalumni.[Last Name], LSPAAalumni.[Suffix], IIf([LSPAAalumni]![Preferred Mailing Code]="H",[LSPAAalumni]![Home Address],[LSPAAalumni]![Work Address]) AS Address, IIf([LSPAAalumni]![Preferred Mailing Code]="H",[LSPAAalumni]![Home Address2],[LSPAAalumni]![Work Address2]) AS Address2, IIf([LSPAAalumni]![Preferred Mailing Code]="H",[LSPAAalumni]![Home City],[LSPAAalumni]![Work City]) AS City, IIf([LSPAAalumni]![Preferred Mailing Code]="H",[LSPAAalumni]![Home State],[LSPAAalumni]![Work State]) AS State, IIf([LSPAAalumni]![Preferred Mailing Code]="H",[LSPAAalumni]![Home Zip],[LSPAAalumni]![Work Zip]) AS Zip
FROM LSPAAalumni;

 

[Remou]

Can you do this as a Union query (typed not tested):

SELECT LSPAAalumni.[Salutation], LSPAAalumni.[First Name], LSPAAalumni.[Last Name], LSPAAalumni.[Suffix], [LSPAAalumni]![Home Address] AS Address, {...} [LSPAAalumni]![Home Zip] AS Zip
FROM LSPAAalumni
WHERE [LSPAAalumni]![Preferred Mailing Code]="H"
UNION 
SELECT LSPAAalumni.[Salutation], LSPAAalumni.[First Name], LSPAAalumni.[Last Name], LSPAAalumni.[Suffix], [LSPAAalumni]![Work Address] AS Address, {...} [LSPAAalumni]![Work Zip] AS Zip
FROM LSPAAalumni
WHERE [LSPAAalumni]![Preferred Mailing Code]="W"

I think with this method it should be easier to add in Title and Company fields. For example:

SELECT {Name}, [LSPAAalumni]![Home Address] AS Address1, {...}, "" As AddressN [LSPAAalumni]![Home Zip] AS Zip
FROM LSPAAalumni
WHERE [LSPAAalumni]![Preferred Mailing Code]="H"
UNION 
SELECT {Name}, [LSPAAalumni]![Title] As Address1, [LSPAAalumni]![Company] As Address2, [LSPAAalumni]![Work Address] AS Address3, {...}, [LSPAAalumni]![Whatever] As AddressN, [LSPAAalumni]![Work Zip] AS Zip
FROM LSPAAalumni
WHERE [LSPAAalumni]![Preferred Mailing Code]="W"

 

[tripleblessed]

The Union worked great until I added pulling the Title & Company fields if code = W. Error msg = # of columns in Union does not match. So, I'm assuming that it doesn't like the fact that I'm pulling 2 extra fields in the 2nd SELECT statement. Since a friend wrote the initial code, what is the difference between the syntax Table.[field] and

![field]? Also, do I need to end the SELECT with the ; like shown below?

Here's what I have:

SELECT LSPAAalumni.[Salutation], LSPAAalumni.[First Name], LSPAAalumni.[Last Name], LSPAAalumni.[Suffix], [LSPAAalumni]![Home Address] AS Address, [LSPAAalumni]![Home Address2] AS Address2, [LSPAAalumni]![Home City] AS City, [LSPAAalumni]![Home State] AS State, [LSPAAalumni]![Home Zip] AS Zip
FROM LSPAAalumni
WHERE [LSPAAalumni]![Preferred Mailing Code]="H"
UNION
SELECT LSPAAalumni.[Salutation], LSPAAalumni.[First Name], LSPAAalumni.[Last Name], LSPAAalumni.[Suffix], LSPAAalumni.[Title], LSPAAalumni.[Company], [LSPAAalumni]![Work Address] AS Address, [LSPAAalumni]![Work Address2] AS Address2, [LSPAAalumni]![Work City] AS City, [LSPAAalumni]![Work State] AS State, [LSPAAalumni]![Work Zip] AS Zip
FROM LSPAAalumni
WHERE [LSPAAalumni]![Preferred Mailing Code]="W";

 

[Remou]

The difference between bang (!) and dot (.) is a long story; you will find debates on the subject of Bang vs Dot, so I'll skip it, if you don't mind, and just say that dot is the usual in the query design window. You can usually live without the semicolon (, as the query designer will fill it in. As for the missing fields, use nulls. For example, I show:
"" As AddressN
In the example above, which serves as a field in the Home selection. The Work selection will have a genuine field at the same place:
[AddressN] As AddressN
I usually find it easiest to build one half of the query first, in this case, the Work side, because it is longest, and then copy and paste in the SQL window. A little messing about should then finish the whole thing off.

 

[tripleblessed]

Thanks Remou! That was it. I appreciate your time and help.

 

[tripleblessed]

Hi Remou, I was deleting old test queries and I somehow messed up the query we were working on. I renamed the query from Mailing Labels Query2 to same name w/o 2. When I go to run it, it asks me for a Parameter Value for Mailing Labels Query2.Last Name. What in the world did I do to it?

 

[Remou]

Open the query in design view and check out the properties (right-click). See if there is an Order By in there, if so get rid of it. If not, check out the parameters (right-click) but this is more unlikely; I think it is a sort.

 

[tripleblessed]

That was it - I had selected Last Name in the ORDER BY field because I'm going to use this query to create address labels. Should I put the ORDER BY [Last Name] in my query somewhere?

 

[tripleblessed]

Never mind - I just realized that I can do the sorting by last name in the Reports/Labels part. Thanks again for all of your help!