IIf query modification

G

Guest

Hi. I have a query that I need to modify and I can't get the syntax correct.

I have members that have 1 or 2 mailing addresses. To determine which
address to use, I have AddressFlag which equals 1 or 2. The query I have is
working correctly, but now I need to add Country into the mix. If Country =
United States, I want my address labels unchanged (meaning, don't display the
country). If Country <> United States, I want the Country to print on the
address label.

This is what I'm working with:
Country: IIf([AddressFlag]=1,c1.Country1,c1.Country2)

Issues:
1) Country is displayed as 1, 2... 1= United States. I have a combo box on
the Country field: SELECT [Country].[CountryID], [Country].[Country] FROM
[Country]
but I want this filed displayed as the actual country (if the country <>
United States) rather than a number.
In my Contacts table where I keep address data, Country1 and Country2 are
text fields. In my Country table I have Country as a text field and use this
as the basis for choosing Country1 and Country2.

2) Do I need to use perhaps Nz along with my IIF statement to not show
Country1 and Country2 if they are United States, or if I have issue 1 fixed
can I just use <>United States in my query?
Thanks for your time!
 
B

Brendan Reynolds

You need to join the Country table into the query to get access (no pun
intended) to the field that contains the name of the country. For example,
if I have a Country table with fields CountryID and CountryName, and a
Member table that includes, among other fields, CountryID, the solution
might look something like this ...

SELECT Member.*, IIf([CountryName]="USA","",[CountryName]) AS Country
FROM Member INNER JOIN Country ON Member.CountryID = Country.CountryID;
 
G

Guest

Thanks- I've fixed one of my issues. Rather than using table Country and
creating a combo box, I'm just going to leave Country1 defaulted as United
States. That way I don't need to worry about having CountryID in my Contacts
(member table) as I really needed 2 references (one for Country1 and one for
Country2). So that's all good.

So, here's my set up- "Labels by Ken HydrantTest" shows Country and is
correct:
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, 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 PostalCode,
IIf([AddressFlag]=1,[c1].[Country1],[c1].[Country2]) AS Country
FROM Contacts AS c1

Then, I'm building my actual label- here's where I want to say, if Country
(which is now either Country1 or Country2) is United States, don't print the
Country. I haven't added Country into the mix as I'm not quite sure how to:

SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,IIf(Len(Trim(LK.[SO Name]) &
"")=0,Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) & Chr(13)
& Chr(10),Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name]) &
Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim(LK.City & ",
" & LK.State & " " & LK.PostalCode)),Trim(LK.[Member Name]) & Chr(13) &
Chr(10) & Trim(LK.[Company Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13) & Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken HydrantTest] AS LK;

Queries get so messy! I'd appreciate your help. Thanks!





Brendan Reynolds said:
You need to join the Country table into the query to get access (no pun
intended) to the field that contains the name of the country. For example,
if I have a Country table with fields CountryID and CountryName, and a
Member table that includes, among other fields, CountryID, the solution
might look something like this ...

SELECT Member.*, IIf([CountryName]="USA","",[CountryName]) AS Country
FROM Member INNER JOIN Country ON Member.CountryID = Country.CountryID;

--
Brendan Reynolds


Stephanie said:
Hi. I have a query that I need to modify and I can't get the syntax
correct.

I have members that have 1 or 2 mailing addresses. To determine which
address to use, I have AddressFlag which equals 1 or 2. The query I have
is
working correctly, but now I need to add Country into the mix. If Country
=
United States, I want my address labels unchanged (meaning, don't display
the
country). If Country <> United States, I want the Country to print on the
address label.

This is what I'm working with:
Country: IIf([AddressFlag]=1,c1.Country1,c1.Country2)

Issues:
1) Country is displayed as 1, 2... 1= United States. I have a combo box
on
the Country field: SELECT [Country].[CountryID], [Country].[Country] FROM
[Country]
but I want this filed displayed as the actual country (if the country <>
United States) rather than a number.
In my Contacts table where I keep address data, Country1 and Country2 are
text fields. In my Country table I have Country as a text field and use
this
as the basis for choosing Country1 and Country2.

2) Do I need to use perhaps Nz along with my IIF statement to not show
Country1 and Country2 if they are United States, or if I have issue 1
fixed
can I just use <>United States in my query?
Thanks for your time!
Click to expand...
Click to expand...
 
B

Brendan Reynolds

So, if I understand correctly, you now have two fields in your Contacts
table, Country1 and Country2. If AddressFlag = 1, you want to display the
value of the Country1 field, unless it is "United States", in which case
display nothing. If AddressFlag = 2, you want to display the value of the
Country2 field, unless it is "United States", in which case display nothing.

SELECT Contact.*, IIf([AddressFlag]=1,IIf([Country1]="United
States","",[Country1]),IIf([Country2]="United States","",[Country2])) AS
Country
FROM Contact;

--
Brendan Reynolds

Stephanie said:
Thanks- I've fixed one of my issues. Rather than using table Country and
creating a combo box, I'm just going to leave Country1 defaulted as United
States. That way I don't need to worry about having CountryID in my
Contacts
(member table) as I really needed 2 references (one for Country1 and one
for
Country2). So that's all good.

So, here's my set up- "Labels by Ken HydrantTest" shows Country and is
correct:
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, 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 PostalCode,
IIf([AddressFlag]=1,[c1].[Country1],[c1].[Country2]) AS Country
FROM Contacts AS c1

Then, I'm building my actual label- here's where I want to say, if Country
(which is now either Country1 or Country2) is United States, don't print
the
Country. I haven't added Country into the mix as I'm not quite sure how
to:

SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,IIf(Len(Trim(LK.[SO Name])
&
"")=0,Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) &
Chr(13)
& Chr(10),Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name])
&
Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim(LK.City &
",
" & LK.State & " " & LK.PostalCode)),Trim(LK.[Member Name]) & Chr(13) &
Chr(10) & Trim(LK.[Company Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13) & Chr(10) & Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken HydrantTest] AS LK;

Queries get so messy! I'd appreciate your help. Thanks!





Brendan Reynolds said:
You need to join the Country table into the query to get access (no pun
intended) to the field that contains the name of the country. For
example,
if I have a Country table with fields CountryID and CountryName, and a
Member table that includes, among other fields, CountryID, the solution
might look something like this ...

SELECT Member.*, IIf([CountryName]="USA","",[CountryName]) AS Country
FROM Member INNER JOIN Country ON Member.CountryID = Country.CountryID;

--
Brendan Reynolds


Stephanie said:
Hi. I have a query that I need to modify and I can't get the syntax
correct.

I have members that have 1 or 2 mailing addresses. To determine which
address to use, I have AddressFlag which equals 1 or 2. The query I
have
is
working correctly, but now I need to add Country into the mix. If
Country
=
United States, I want my address labels unchanged (meaning, don't
display
the
country). If Country <> United States, I want the Country to print on
the
address label.

This is what I'm working with:
Country: IIf([AddressFlag]=1,c1.Country1,c1.Country2)

Issues:
1) Country is displayed as 1, 2... 1= United States. I have a combo
box
on
the Country field: SELECT [Country].[CountryID], [Country].[Country]
FROM
[Country]
but I want this filed displayed as the actual country (if the country
<>
United States) rather than a number.
In my Contacts table where I keep address data, Country1 and Country2
are
text fields. In my Country table I have Country as a text field and
use
this
as the basis for choosing Country1 and Country2.

2) Do I need to use perhaps Nz along with my IIF statement to not show
Country1 and Country2 if they are United States, or if I have issue 1
fixed
can I just use <>United States in my query?
Thanks for your time!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks for the reply. Your understanding is correct. My first query lists
all contacts that meet the criteria and shows their country.

But ultimately, I'm trying to incorporate the first query into my label
query- trying to embed an IIf statement into my existing IIf statement.

SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,IIf(Len(Trim(LK.[SO Name]) &
"")=0,Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) & Chr(13)
& Chr(10),Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name]) &
Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim(LK.City & ",
" & LK.State & " " & LK.PostalCode)),Trim(LK.[Member Name]) & Chr(13) &
Chr(10) & Trim(LK.[Company Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13) & Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken HydrantTest] AS LK;

In the label query above, I don't want to exclude anyone that the first
query provided. But, if the contact lives in Canada, I want the mail labels
to list his country. If the contact lives in the United States, I want the
mail label to skip over the country (so as to not confuse the US postal
service). Currently, my label query doesn't have Country at all, as I'm not
sure how to embed it into the existing IIf statement and get the new country
behavior that I need.

Hope that makes sense. I appreciate your help. Thanks!

Brendan Reynolds said:
So, if I understand correctly, you now have two fields in your Contacts
table, Country1 and Country2. If AddressFlag = 1, you want to display the
value of the Country1 field, unless it is "United States", in which case
display nothing. If AddressFlag = 2, you want to display the value of the
Country2 field, unless it is "United States", in which case display nothing.

SELECT Contact.*, IIf([AddressFlag]=1,IIf([Country1]="United
States","",[Country1]),IIf([Country2]="United States","",[Country2])) AS
Country
FROM Contact;

--
Brendan Reynolds

Stephanie said:
Thanks- I've fixed one of my issues. Rather than using table Country and
creating a combo box, I'm just going to leave Country1 defaulted as United
States. That way I don't need to worry about having CountryID in my
Contacts
(member table) as I really needed 2 references (one for Country1 and one
for
Country2). So that's all good.

So, here's my set up- "Labels by Ken HydrantTest" shows Country and is
correct:
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, 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 PostalCode,
IIf([AddressFlag]=1,[c1].[Country1],[c1].[Country2]) AS Country
FROM Contacts AS c1

Then, I'm building my actual label- here's where I want to say, if Country
(which is now either Country1 or Country2) is United States, don't print
the
Country. I haven't added Country into the mix as I'm not quite sure how
to:

SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,IIf(Len(Trim(LK.[SO Name])
&
"")=0,Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) &
Chr(13)
& Chr(10),Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name])
&
Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim(LK.City &
",
" & LK.State & " " & LK.PostalCode)),Trim(LK.[Member Name]) & Chr(13) &
Chr(10) & Trim(LK.[Company Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13) & Chr(10) & Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken HydrantTest] AS LK;

Queries get so messy! I'd appreciate your help. Thanks!





Brendan Reynolds said:
You need to join the Country table into the query to get access (no pun
intended) to the field that contains the name of the country. For
example,
if I have a Country table with fields CountryID and CountryName, and a
Member table that includes, among other fields, CountryID, the solution
might look something like this ...

SELECT Member.*, IIf([CountryName]="USA","",[CountryName]) AS Country
FROM Member INNER JOIN Country ON Member.CountryID = Country.CountryID;

--
Brendan Reynolds


Hi. I have a query that I need to modify and I can't get the syntax
correct.

I have members that have 1 or 2 mailing addresses. To determine which
address to use, I have AddressFlag which equals 1 or 2. The query I
have
is
working correctly, but now I need to add Country into the mix. If
Country
=
United States, I want my address labels unchanged (meaning, don't
display
the
country). If Country <> United States, I want the Country to print on
the
address label.

This is what I'm working with:
Country: IIf([AddressFlag]=1,c1.Country1,c1.Country2)

Issues:
1) Country is displayed as 1, 2... 1= United States. I have a combo
box
on
the Country field: SELECT [Country].[CountryID], [Country].[Country]
FROM
[Country]
but I want this filed displayed as the actual country (if the country
<>
United States) rather than a number.
In my Contacts table where I keep address data, Country1 and Country2
are
text fields. In my Country table I have Country as a text field and
use
this
as the basis for choosing Country1 and Country2.

2) Do I need to use perhaps Nz along with my IIF statement to not show
Country1 and Country2 if they are United States, or if I have issue 1
fixed
can I just use <>United States in my query?
Thanks for your time!
Click to expand...
Click to expand...
Click to expand...
 
B

Brendan Reynolds

Unless I'm missing something, Stephanie (which is, of course, always a
possibility) that's exactly what the example in my previous post does.

For testing, I set up a table called Contact with fields ContactID
(AutoNumber, PK), AddressFlag (Long Integer), Country1 (Text) and Country2
(Text). I then added the following four test records ...

1, 1, Ireland, Narnia
2, 1, United States, Erehwon
3, 2, Mordor, Ireland
4, 2, Aquilonia, United States

The expected result is ...

1, Ireland - because AddressFlag = 1 and Country1 (Ireland) <> United States
2, an empty string - because AddressFlag = 1 and Country1 = United States
3, Ireland - because AddressFlag = 2 and Country2 (Ireland) <> United States
4, an empty string - because AddressFlag = 2 and Country2 = United States

The query from my previous post ...

SELECT Contact.*, IIf([AddressFlag]=1,IIf([Country1]="United
States","",[Country1]),IIf([Country2]="United States","",[Country2])) AS
Country
FROM Contact;

.... returns precisely these expected results.

--
Brendan Reynolds


Stephanie said:
Thanks for the reply. Your understanding is correct. My first query
lists
all contacts that meet the criteria and shows their country.

But ultimately, I'm trying to incorporate the first query into my label
query- trying to embed an IIf statement into my existing IIf statement.

SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,IIf(Len(Trim(LK.[SO Name])
&
"")=0,Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) &
Chr(13)
& Chr(10),Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name])
&
Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim(LK.City &
",
" & LK.State & " " & LK.PostalCode)),Trim(LK.[Member Name]) & Chr(13) &
Chr(10) & Trim(LK.[Company Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13) & Chr(10) & Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken HydrantTest] AS LK;

In the label query above, I don't want to exclude anyone that the first
query provided. But, if the contact lives in Canada, I want the mail
labels
to list his country. If the contact lives in the United States, I want
the
mail label to skip over the country (so as to not confuse the US postal
service). Currently, my label query doesn't have Country at all, as I'm
not
sure how to embed it into the existing IIf statement and get the new
country
behavior that I need.

Hope that makes sense. I appreciate your help. Thanks!

Brendan Reynolds said:
So, if I understand correctly, you now have two fields in your Contacts
table, Country1 and Country2. If AddressFlag = 1, you want to display the
value of the Country1 field, unless it is "United States", in which case
display nothing. If AddressFlag = 2, you want to display the value of the
Country2 field, unless it is "United States", in which case display
nothing.

SELECT Contact.*, IIf([AddressFlag]=1,IIf([Country1]="United
States","",[Country1]),IIf([Country2]="United States","",[Country2])) AS
Country
FROM Contact;

--
Brendan Reynolds

Stephanie said:
Thanks- I've fixed one of my issues. Rather than using table Country
and
creating a combo box, I'm just going to leave Country1 defaulted as
United
States. That way I don't need to worry about having CountryID in my
Contacts
(member table) as I really needed 2 references (one for Country1 and
one
for
Country2). So that's all good.

So, here's my set up- "Labels by Ken HydrantTest" shows Country and is
correct:
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, 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
PostalCode,
IIf([AddressFlag]=1,[c1].[Country1],[c1].[Country2]) AS Country
FROM Contacts AS c1

Then, I'm building my actual label- here's where I want to say, if
Country
(which is now either Country1 or Country2) is United States, don't
print
the
Country. I haven't added Country into the mix as I'm not quite sure
how
to:

SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,IIf(Len(Trim(LK.[SO
Name])
&
"")=0,Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) &
Chr(13)
& Chr(10),Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.[SO
Name])
&
Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim(LK.City
&
",
" & LK.State & " " & LK.PostalCode)),Trim(LK.[Member Name]) & Chr(13) &
Chr(10) & Trim(LK.[Company Name]) & Chr(13) & Chr(10) &
Trim(LK.Address) &
Chr(13) & Chr(10) & Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken HydrantTest] AS LK;

Queries get so messy! I'd appreciate your help. Thanks!





:

You need to join the Country table into the query to get access (no
pun
intended) to the field that contains the name of the country. For
example,
if I have a Country table with fields CountryID and CountryName, and a
Member table that includes, among other fields, CountryID, the
solution
might look something like this ...

SELECT Member.*, IIf([CountryName]="USA","",[CountryName]) AS Country
FROM Member INNER JOIN Country ON Member.CountryID =
Country.CountryID;

--
Brendan Reynolds


Hi. I have a query that I need to modify and I can't get the syntax
correct.

I have members that have 1 or 2 mailing addresses. To determine
which
address to use, I have AddressFlag which equals 1 or 2. The query I
have
is
working correctly, but now I need to add Country into the mix. If
Country
=
United States, I want my address labels unchanged (meaning, don't
display
the
country). If Country <> United States, I want the Country to print
on
the
address label.

This is what I'm working with:
Country: IIf([AddressFlag]=1,c1.Country1,c1.Country2)

Issues:
1) Country is displayed as 1, 2... 1= United States. I have a combo
box
on
the Country field: SELECT [Country].[CountryID], [Country].[Country]
FROM
[Country]
but I want this filed displayed as the actual country (if the
country
<>
United States) rather than a number.
In my Contacts table where I keep address data, Country1 and
Country2
are
text fields. In my Country table I have Country as a text field and
use
this
as the basis for choosing Country1 and Country2.

2) Do I need to use perhaps Nz along with my IIF statement to not
show
Country1 and Country2 if they are United States, or if I have issue
1
fixed
can I just use <>United States in my query?
Thanks for your time!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Wow! Thanks for your efforts- I appreciate it!
Thanks for the query help- that did the trick. My last issue was how to get
the parameter:
IIf([AddressFlag]=1,IIf([Country1]="United
States","",[Country1]),IIf([Country2]="United States","",[Country2])) AS
Country

embedded into my label format:
Trim(LK.City & ", " & LK.State & " " & LK.PostalCode & " " &LK.Country)

I figured that part out, but would never have gotten the sql correct without
your help. Thanks!


Brendan Reynolds said:
Unless I'm missing something, Stephanie (which is, of course, always a
possibility) that's exactly what the example in my previous post does.

For testing, I set up a table called Contact with fields ContactID
(AutoNumber, PK), AddressFlag (Long Integer), Country1 (Text) and Country2
(Text). I then added the following four test records ...

1, 1, Ireland, Narnia
2, 1, United States, Erehwon
3, 2, Mordor, Ireland
4, 2, Aquilonia, United States

The expected result is ...

1, Ireland - because AddressFlag = 1 and Country1 (Ireland) <> United States
2, an empty string - because AddressFlag = 1 and Country1 = United States
3, Ireland - because AddressFlag = 2 and Country2 (Ireland) <> United States
4, an empty string - because AddressFlag = 2 and Country2 = United States

The query from my previous post ...

SELECT Contact.*, IIf([AddressFlag]=1,IIf([Country1]="United
States","",[Country1]),IIf([Country2]="United States","",[Country2])) AS
Country
FROM Contact;

.... returns precisely these expected results.

--
Brendan Reynolds


Stephanie said:
Thanks for the reply. Your understanding is correct. My first query
lists
all contacts that meet the criteria and shows their country.

But ultimately, I'm trying to incorporate the first query into my label
query- trying to embed an IIf statement into my existing IIf statement.

SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,IIf(Len(Trim(LK.[SO Name])
&
"")=0,Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) &
Chr(13)
& Chr(10),Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name])
&
Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim(LK.City &
",
" & LK.State & " " & LK.PostalCode)),Trim(LK.[Member Name]) & Chr(13) &
Chr(10) & Trim(LK.[Company Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13) & Chr(10) & Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken HydrantTest] AS LK;

In the label query above, I don't want to exclude anyone that the first
query provided. But, if the contact lives in Canada, I want the mail
labels
to list his country. If the contact lives in the United States, I want
the
mail label to skip over the country (so as to not confuse the US postal
service). Currently, my label query doesn't have Country at all, as I'm
not
sure how to embed it into the existing IIf statement and get the new
country
behavior that I need.

Hope that makes sense. I appreciate your help. Thanks!

Brendan Reynolds said:
So, if I understand correctly, you now have two fields in your Contacts
table, Country1 and Country2. If AddressFlag = 1, you want to display the
value of the Country1 field, unless it is "United States", in which case
display nothing. If AddressFlag = 2, you want to display the value of the
Country2 field, unless it is "United States", in which case display
nothing.

SELECT Contact.*, IIf([AddressFlag]=1,IIf([Country1]="United
States","",[Country1]),IIf([Country2]="United States","",[Country2])) AS
Country
FROM Contact;

--
Brendan Reynolds

Thanks- I've fixed one of my issues. Rather than using table Country
and
creating a combo box, I'm just going to leave Country1 defaulted as
United
States. That way I don't need to worry about having CountryID in my
Contacts
(member table) as I really needed 2 references (one for Country1 and
one
for
Country2). So that's all good.

So, here's my set up- "Labels by Ken HydrantTest" shows Country and is
correct:
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, 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
PostalCode,
IIf([AddressFlag]=1,[c1].[Country1],[c1].[Country2]) AS Country
FROM Contacts AS c1

Then, I'm building my actual label- here's where I want to say, if
Country
(which is now either Country1 or Country2) is United States, don't
print
the
Country. I haven't added Country into the mix as I'm not quite sure
how
to:

SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,IIf(Len(Trim(LK.[SO
Name])
&
"")=0,Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) &
Chr(13)
& Chr(10),Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.[SO
Name])
&
Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim(LK.City
&
",
" & LK.State & " " & LK.PostalCode)),Trim(LK.[Member Name]) & Chr(13) &
Chr(10) & Trim(LK.[Company Name]) & Chr(13) & Chr(10) &
Trim(LK.Address) &
Chr(13) & Chr(10) & Trim(LK.City & ", " & LK.State & " " &
LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken HydrantTest] AS LK;

Queries get so messy! I'd appreciate your help. Thanks!





:

You need to join the Country table into the query to get access (no
pun
intended) to the field that contains the name of the country. For
example,
if I have a Country table with fields CountryID and CountryName, and a
Member table that includes, among other fields, CountryID, the
solution
might look something like this ...

SELECT Member.*, IIf([CountryName]="USA","",[CountryName]) AS Country
FROM Member INNER JOIN Country ON Member.CountryID =
Country.CountryID;

--
Brendan Reynolds


Hi. I have a query that I need to modify and I can't get the syntax
correct.

I have members that have 1 or 2 mailing addresses. To determine
which
address to use, I have AddressFlag which equals 1 or 2. The query I
have
is
working correctly, but now I need to add Country into the mix. If
Country
=
United States, I want my address labels unchanged (meaning, don't
display
the
country). If Country <> United States, I want the Country to print
on
the
address label.

This is what I'm working with:
Country: IIf([AddressFlag]=1,c1.Country1,c1.Country2)

Issues:
1) Country is displayed as 1, 2... 1= United States. I have a combo
box
on
the Country field: SELECT [Country].[CountryID], [Country].[Country]
FROM
[Country]
but I want this filed displayed as the actual country (if the
country
<>
United States) rather than a number.
In my Contacts table where I keep address data, Country1 and
Country2
are
text fields. In my Country table I have Country as a text field and
use
this
as the basis for choosing Country1 and Country2.

2) Do I need to use perhaps Nz along with my IIF statement to not
show
Country1 and Country2 if they are United States, or if I have issue
1
fixed
can I just use <>United States in my query?
Thanks for your time!
Click to expand...
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 to merge text values from two columns and DLookUp text value in another table 4
ArrayList Issue 4
how to allow the user to enter their search criteria 2
Delete "United States of America" from the Country/Region 4
Problem with criteria and trouble with instr() 6
How to copy and paste only visible cells from Excel using vbscript 0
Mail Merge - Insert Address Block - Insert Country 4
If Column A = X then put in Column B Y 3

Top