Conditional statement

[Guest]

Hello. I originally planned on having CompanyName for those companies who are
members. But the users have been entering CompanyName for both companies who
are members and as the place where an individual member works.

So my mailing label to an individual's home address includes the name of the
company where they work.
I'd like to update my query to say if the DonorType is "CU" or "IN"
(customer or individual), don't include CompanyName. Queries challenge me.
Embedded queries stump me. I'd appreciate your suggestions... Thanks!

SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member Name],
c1.DonorTypeID, Nz(c1.CompanyName," ") AS [Company Name], c1.ContactLastName,
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
FROM Contacts AS c1
WHERE ....

 

[Carl Rapson]

Hello. I originally planned on having CompanyName for those companies who
are
members. But the users have been entering CompanyName for both companies
who
are members and as the place where an individual member works.

So my mailing label to an individual's home address includes the name of
the
company where they work.
I'd like to update my query to say if the DonorType is "CU" or "IN"
(customer or individual), don't include CompanyName. Queries challenge me.
Embedded queries stump me. I'd appreciate your suggestions... Thanks!

SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member Name],
c1.DonorTypeID, Nz(c1.CompanyName," ") AS [Company Name],
c1.ContactLastName,
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
FROM Contacts AS c1
WHERE ....

I think the best you can do is return a blank for the Company Name; I don't
think you can completely eliminate the Company Name field from the query.
Try something like:

SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member Name],
c1.DonorTypeID,
IIf(([DonorType]="CU" or [DonorType]="IN"),"",Nz(c1.CompanyName,"")) AS
[Company Name],
c1.ContactLastName,
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
FROM Contacts AS c1
WHERE ....

You'll still have a Company Name field, but it will be empty if the
DonorType is CU or IN.

Carl Rapson

 

[Guest]

Carl,
I'm so sorry I haven't responded- I shouldn't trust the "notify" feature.
Thanks for taking the time to reply- I appreciate the help.
I'll give your suggestion a try!

Hello. I originally planned on having CompanyName for those companies who
are
members. But the users have been entering CompanyName for both companies
who
are members and as the place where an individual member works.

So my mailing label to an individual's home address includes the name of
the
company where they work.
I'd like to update my query to say if the DonorType is "CU" or "IN"
(customer or individual), don't include CompanyName. Queries challenge me.
Embedded queries stump me. I'd appreciate your suggestions... Thanks!

SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member Name],
c1.DonorTypeID, Nz(c1.CompanyName," ") AS [Company Name],
c1.ContactLastName,
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
FROM Contacts AS c1
WHERE ....

I think the best you can do is return a blank for the Company Name; I don't
think you can completely eliminate the Company Name field from the query.
Try something like:

SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member Name],
c1.DonorTypeID,
IIf(([DonorType]="CU" or [DonorType]="IN"),"",Nz(c1.CompanyName,"")) AS
[Company Name],
c1.ContactLastName,
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
FROM Contacts AS c1
WHERE ....

You'll still have a Company Name field, but it will be empty if the
DonorType is CU or IN.

Carl Rapson