Query not limited as expected

S

SillySally

Hello. I have a query that uses an input parameter.
I've modified the query and can't figure out why it is no
longer being limited by the parameter. The user selects
the State and mailing labels are limited to that State.

The unbound input form (LabelsParam) has field FindLabels
with control source:
SELECT DISTINCT IIf([AddressFlag]=1,UCase
([StateOrProvince1]),UCase([StateOrProvince2])) AS State
FROM Contacts;

The Query:
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, c1.SignificantOtherID, c1.Member
FROM Contacts AS c1
WHERE (((c1.SignificantOtherID)>[c1].[ContactID]) AND
((IIf([AddressFlag]=1,UCase([c1].[StateOrProvince1]),UCase
([c1].[StateOrProvince2])))=[Forms]![LabelsParam]!
[FindLabels])) OR (((c1.SignificantOtherID) Is Null) AND
((c1.Member)=Yes));

The query is returning all members = yes, regardless of
their State. Any suggestions about what I'm doing
incorrectly? Thanks.
 
J

John Spencer (MVP)

If I've broken the query's where clause down correctly you have

SELECT ...
FROM Contacts AS c1
WHERE (c1.SignificantOtherID>[c1].[ContactID] AND
IIf([AddressFlag]=1,
UCase([c1].[StateOrProvince1]),
UCase([c1].[StateOrProvince2]))=[Forms]![LabelsParam]![FindLabels])

OR (c1.SignificantOtherID Is Null AND c1.Member=Yes)

That will return all records where SignificantOtherID is null and member = yes
PLUS
all records where the stateOrProvince Matches the selected state/province and significantId>ContactID

Do you want the OR to be an AND or do you want the state criteria to apply to
the both parts of the where clause. You might try something like the following

SELECT ...
FROM Contacts AS c1
WHERE (c1.SignificantOtherID>[c1].[ContactID]
OR (c1.SignificantOtherID Is Null AND c1.Member=Yes))
AND IIf([AddressFlag]=1,
UCase([c1].[StateOrProvince1]),
UCase([c1].[StateOrProvince2]))=[Forms]![LabelsParam]![FindLabels])
 
S

SteveS

SillySally said:
Hello. I have a query that uses an input parameter.
I've modified the query and can't figure out why it is no
longer being limited by the parameter. The user selects
the State and mailing labels are limited to that State.

The unbound input form (LabelsParam) has field FindLabels
with control source:
SELECT DISTINCT IIf([AddressFlag]=1,UCase
([StateOrProvince1]),UCase([StateOrProvince2])) AS State
FROM Contacts;

The Query:
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, c1.SignificantOtherID, c1.Member
FROM Contacts AS c1
WHERE (((c1.SignificantOtherID)>[c1].[ContactID]) AND
((IIf([AddressFlag]=1,UCase([c1].[StateOrProvince1]),UCase
([c1].[StateOrProvince2])))=[Forms]![LabelsParam]!
[FindLabels])) OR (((c1.SignificantOtherID) Is Null) AND
((c1.Member)=Yes));

The query is returning all members = yes, regardless of
their State. Any suggestions about what I'm doing
incorrectly? Thanks.
Click to expand...

That rotten syntax!! How come the computer doesn't do what I want, how
come it does what I tell it??? <g>


The WHERE clause requires syntax like: Table.FieldName = something

You have a function equal to a parameter. So the IIF() returns a city
name and the parameter (from the control) returns a city name, so you
have Seattle = Seattle. With the rest of the tests, it looks like you
get what you ask for.


Try changing the IIF() so it looks like this:

UCase(C1.StateOrProvince1) = [Forms]![LabelsParam]![FindLabels]


Is c1.Member=Yes a string? IF so, then you should use "Yes". If it is
boolean (Yes/No), then (I think) you should use TRUE.

It also looks like the "OR" is in the wrong place. The city (what should
be the city) is OR'd with (c1.SignificantOtherID) Is Null). If either
one is TRUE, and the other parts are TRUE (AND'd), the record will be
returned.


This might be closer to what you want(?):

WHERE (c1.SignificantOtherID>[c1].[ContactID] OR c1.SignificantOtherID
Is Null) AND UCase([c1].[StateOrProvince2]) =
[Forms]![LabelsParam]![FindLabels] AND c1.Member=Yes;


HTH
 
S

SillySally

Thanks- sometimes I get so lost in the parentheses that I
forget to break the query into pieces. Thanks for the
tips!
-----Original Message-----
If I've broken the query's where clause down correctly you have

SELECT ...
FROM Contacts AS c1
WHERE (c1.SignificantOtherID>[c1].[ContactID] AND
IIf([AddressFlag]=1,
UCase([c1].[StateOrProvince1]),
UCase([c1].[StateOrProvince2]))=[Forms]! [LabelsParam]![FindLabels])

OR (c1.SignificantOtherID Is Null AND c1.Member=Yes)

That will return all records where SignificantOtherID is null and member = yes
PLUS
all records where the stateOrProvince Matches the
Click to expand...
selected state/province and significantId>ContactID
Do you want the OR to be an AND or do you want the state criteria to apply to
the both parts of the where clause. You might try something like the following

SELECT ...
FROM Contacts AS c1
WHERE (c1.SignificantOtherID>[c1].[ContactID]
OR (c1.SignificantOtherID Is Null AND c1.Member=Yes))
AND IIf([AddressFlag]=1,
UCase([c1].[StateOrProvince1]),
UCase([c1].[StateOrProvince2]))=[Forms]! [LabelsParam]![FindLabels])
Hello. I have a query that uses an input parameter.
I've modified the query and can't figure out why it is no
longer being limited by the parameter. The user selects
the State and mailing labels are limited to that State.

The unbound input form (LabelsParam) has field FindLabels
with control source:
SELECT DISTINCT IIf([AddressFlag]=1,UCase
([StateOrProvince1]),UCase([StateOrProvince2])) AS State
FROM Contacts;

The Query:
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, c1.SignificantOtherID, c1.Member
FROM Contacts AS c1
WHERE (((c1.SignificantOtherID)>[c1].[ContactID]) AND
((IIf([AddressFlag]=1,UCase([c1]. [StateOrProvince1]),UCase
([c1].[StateOrProvince2])))=[Forms]![LabelsParam]!
[FindLabels])) OR (((c1.SignificantOtherID) Is Null) AND
((c1.Member)=Yes));

The query is returning all members = yes, regardless of
their State. Any suggestions about what I'm doing
incorrectly? Thanks.
Click to expand...
.
Click to expand...
 
S

SillySally

Rotten syntax, indeed! The AND fixed the problem. Thanks.
-----Original Message-----
SillySally said:
Hello. I have a query that uses an input parameter.
I've modified the query and can't figure out why it is no
longer being limited by the parameter. The user selects
the State and mailing labels are limited to that State.

The unbound input form (LabelsParam) has field FindLabels
with control source:
SELECT DISTINCT IIf([AddressFlag]=1,UCase
([StateOrProvince1]),UCase([StateOrProvince2])) AS State
FROM Contacts;

The Query:
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, c1.SignificantOtherID, c1.Member
FROM Contacts AS c1
WHERE (((c1.SignificantOtherID)>[c1].[ContactID]) AND
((IIf([AddressFlag]=1,UCase([c1]. [StateOrProvince1]),UCase
([c1].[StateOrProvince2])))=[Forms]![LabelsParam]!
[FindLabels])) OR (((c1.SignificantOtherID) Is Null) AND
((c1.Member)=Yes));

The query is returning all members = yes, regardless of
their State. Any suggestions about what I'm doing
incorrectly? Thanks.
Click to expand...

That rotten syntax!! How come the computer doesn't do what I want, how
come it does what I tell it??? <g>


The WHERE clause requires syntax like: Table.FieldName = something

You have a function equal to a parameter. So the IIF() returns a city
name and the parameter (from the control) returns a city name, so you
have Seattle = Seattle. With the rest of the tests, it looks like you
get what you ask for.


Try changing the IIF() so it looks like this:

UCase(C1.StateOrProvince1) = [Forms]![LabelsParam]! [FindLabels]


Is c1.Member=Yes a string? IF so, then you should use "Yes". If it is
boolean (Yes/No), then (I think) you should use TRUE.

It also looks like the "OR" is in the wrong place. The city (what should
be the city) is OR'd with (c1.SignificantOtherID) Is Null). If either
one is TRUE, and the other parts are TRUE (AND'd), the record will be
returned.


This might be closer to what you want(?):

WHERE (c1.SignificantOtherID>[c1].[ContactID] OR c1.SignificantOtherID
Is Null) AND UCase([c1].[StateOrProvince2]) =
[Forms]![LabelsParam]![FindLabels] AND c1.Member=Yes;


HTH
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

IIF in select 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