IIF in select

S

SillySally

Hi. I'm trying to add an IIF statement within an existing
select statement. I'm building mailing labels and I'm
tyring to say that if AddressFlag = 1, use
MailingAddress1, OptAddress1, City1, StateOrProvince1 and
PostalCode1. And if AddressFlag = 2, use these fields
with the "2" desgination (e.g. MailingAddress2).

IIf(([AddressFlag]=1), c1.MailingAddress1
& ", "+c1.OptAddress1 AS Address, c1.City1, UCase
(c1.StateOrProvince1) AS State, c1.PostalCode1)

This gets hung up on on AS Address. And I haven't even
gotten to AddressFlag=2!

I've posted the sql I'm trying to adjust for my new
address considerations:
SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS
[Member Name], (SELECT Nz(c2.[NickName],c2.[FirstName])
& " " & c2.[LastName] AS [SONm] FROM Contacts c2 WHERE
c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], Nz(c1.CompanyName," ") AS [Company Name], Nz
(c1.ContactNickName,c1.ContactFirstName) & " " &
c1.ContactLastName AS ContactName, c1.MailingAddress
& ", "+c1.OptAddress AS Address, c1.City, UCase
(c1.StateOrProvince) AS State, c1.PostalCode,
c1.SignificantOtherID, c1.SignificantOtherID, c1.Newsletter
FROM Contacts AS c1
WHERE (c1.SignificantOtherID>c1.ContactID Or
c1.SignificantOtherID Is Null And c1.Newsletter=Yes)
ORDER BY c1.PostalCode;

Can I just plop an IIF in the middle of the select?
Thanks for the help!
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Two ways: 1) Use the IIf() function for each column; 2) Concatenate all
the address info into one column.

1) IIf() for each column:

IIf(AddressFlag=1, c1.MailingAddress1 & ", " & c1.OptAddress1,
c1.MailingAddress2 & ", " & c1.OptAddress2) AS Address,

IIf(AddressFlag=1, c1.City1, c1.City2) As City,

IIf(AddressFlag=1, UCase(c1.StateOrProvince1) & " " & c1.PostalCode1,
UCase(c1.StateOrProvince2) & " " & c1.PostalCode2) As PostalArea,


2) Use IIf(flag = 1, address1, address2). I broke it up so you can see
each True & False expressions:

IIf([AddressFlag]=1),

c1.MailingAddress1 & ", " & c1.OptAddress1 & chr$(13) & c1.City1 & ", "
& UCase(c1.StateOrProvince1) & " " & c1.PostalCode1,

c1.MailingAddress2 & ", " & c1.OptAddress2 & chr$(13) & c1.City2 & ", "
& UCase(c1.StateOrProvince2) & " " & c1.PostalCode2
) As Address,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQj4/xYechKqOuFEgEQKAcACfUMrM5zJtcT4tKUc/DxJdRXglpdkAnjO3
JML9pP/tM0nSOdB4HqyZPPTi
=r51p
-----END PGP SIGNATURE-----
 
S

SteveS

SillySally said:
Hi. I'm trying to add an IIF statement within an existing
select statement. I'm building mailing labels and I'm
tyring to say that if AddressFlag = 1, use
MailingAddress1, OptAddress1, City1, StateOrProvince1 and
PostalCode1. And if AddressFlag = 2, use these fields
with the "2" desgination (e.g. MailingAddress2).

IIf(([AddressFlag]=1), c1.MailingAddress1
& ", "+c1.OptAddress1 AS Address, c1.City1, UCase
(c1.StateOrProvince1) AS State, c1.PostalCode1)

This gets hung up on on AS Address. And I haven't even
gotten to AddressFlag=2!

I've posted the sql I'm trying to adjust for my new
address considerations:
SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS
[Member Name], (SELECT Nz(c2.[NickName],c2.[FirstName])
& " " & c2.[LastName] AS [SONm] FROM Contacts c2 WHERE
c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], Nz(c1.CompanyName," ") AS [Company Name], Nz
(c1.ContactNickName,c1.ContactFirstName) & " " &
c1.ContactLastName AS ContactName, c1.MailingAddress
& ", "+c1.OptAddress AS Address, c1.City, UCase
(c1.StateOrProvince) AS State, c1.PostalCode,
c1.SignificantOtherID, c1.SignificantOtherID, c1.Newsletter
FROM Contacts AS c1
WHERE (c1.SignificantOtherID>c1.ContactID Or
c1.SignificantOtherID Is Null And c1.Newsletter=Yes)
ORDER BY c1.PostalCode;

Can I just plop an IIF in the middle of the select?
Thanks for the help!
Click to expand...

You aren't using the proper syntax for the Immediate IF function.
It is:

IIF(condition, TRUE_Part, FALSE_Part)


The equivalent VBA If..Then..Else statement would be

If condition Then
TRUE_Part
Else
FALSE_Part
End If

You can't assign the TRUE_Part a name (As Address). It has to be out
side of the function.

Assuming AddressFlag can only be 1 or 2, It is going to take at least
four IIF() 's:


IIf([AddressFlag]=1, c1.MailingAddress1 & ", "& c1.OptAddress1,
c1.MailingAddress2 & ", "& c1.OptAddress2) As Address

IIf([AddressFlag]=1, c1.City1, c1.City2) As City

IIf([AddressFlag]=1, UCase(c1.StateOrProvince1),
UCase(c1.StateOrProvince2)) As State

IIf([AddressFlag]=1, c1.PostalCode1, c1.PostalCode2) As Zip



Remember to use "&" when concatenating strings, not "+".

Also, I noticed that you have 2 c1.SignificantOtherID's in the SQL code.

HTH
 
S

SillySally

Steve,
Thanks for the fabulous explanation! Likening IIF to If,
Then, Else makes sense. I kept wondering how so use
a "then". Thanks for pointing out my duplication. I do
believe that "+" keeps me from having an errant "," if I
don't have an OptAddress. Without it I was getting

John Doe
123 Wall St.,
Any Town, US

Thanks for the help- I appreciate it!
 
S

SillySally

Thanks for the help! I was able to create an IIf
statement that worked. I appreciate you showing me two
ways to get it done- Thanks!
-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Two ways: 1) Use the IIf() function for each column; 2) Concatenate all
the address info into one column.

1) IIf() for each column:

IIf(AddressFlag=1, c1.MailingAddress1 & ", " & c1.OptAddress1,
c1.MailingAddress2 & ", " & c1.OptAddress2) AS Address,

IIf(AddressFlag=1, c1.City1, c1.City2) As City,

IIf(AddressFlag=1, UCase(c1.StateOrProvince1) & " " & c1.PostalCode1,
UCase(c1.StateOrProvince2) & " " & c1.PostalCode2) As PostalArea,


2) Use IIf(flag = 1, address1, address2). I broke it up so you can see
each True & False expressions:

IIf([AddressFlag]=1),

c1.MailingAddress1 & ", " & c1.OptAddress1 & chr$(13) & c1.City1 & ", "
& UCase(c1.StateOrProvince1) & " " & c1.PostalCode1,

c1.MailingAddress2 & ", " & c1.OptAddress2 & chr$(13) & c1.City2 & ", "
& UCase(c1.StateOrProvince2) & " " & c1.PostalCode2
) As Address,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQj4/xYechKqOuFEgEQKAcACfUMrM5zJtcT4tKUc/DxJdRXglpd kAnjO3
JML9pP/tM0nSOdB4HqyZPPTi
=r51p
-----END PGP SIGNATURE-----

Hi. I'm trying to add an IIF statement within an existing
select statement. I'm building mailing labels and I'm
tyring to say that if AddressFlag = 1, use
MailingAddress1, OptAddress1, City1, StateOrProvince1 and
PostalCode1. And if AddressFlag = 2, use these fields
with the "2" desgination (e.g. MailingAddress2).

IIf(([AddressFlag]=1), c1.MailingAddress1
& ", "+c1.OptAddress1 AS Address, c1.City1, UCase
(c1.StateOrProvince1) AS State, c1.PostalCode1)

This gets hung up on on AS Address. And I haven't even
gotten to AddressFlag=2!

I've posted the sql I'm trying to adjust for my new
address considerations:
SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS
[Member Name], (SELECT Nz(c2.[NickName],c2.[FirstName])
& " " & c2.[LastName] AS [SONm] FROM Contacts c2 WHERE
c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], Nz(c1.CompanyName," ") AS [Company Name], Nz
(c1.ContactNickName,c1.ContactFirstName) & " " &
c1.ContactLastName AS ContactName, c1.MailingAddress
& ", "+c1.OptAddress AS Address, c1.City, UCase
(c1.StateOrProvince) AS State, c1.PostalCode,
c1.SignificantOtherID, c1.SignificantOtherID, c1.Newsletter
FROM Contacts AS c1
WHERE (c1.SignificantOtherID>c1.ContactID Or
c1.SignificantOtherID Is Null And c1.Newsletter=Yes)
ORDER BY c1.PostalCode;

Can I just plop an IIF in the middle of the select?
Thanks for the help!
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query not limited as expected 4
Conditional statement 2
sql union self-join syntax 27
Name order streamlining 21
Oh, Canada! Mailing address issues. 10
Self join? 1
IIf query modification 6
Adding a carriage return 6

Top