problem getting a query to recognize "Not" and "<>" in a value list combo box

[Paul]

I have a combo box in the form header of a continuous form that I'm trying
to use to filter records displayed in the form. I'm trying to use the
following text as the Row Source for the control:

"*";"All leases";"ABC";"ABC leases only";"<>ABC";"Non-ABC leases"

The first two choices, All leases and ABC leases only work fine. That is,
the recordset only displays the appropriate records for those choices. It's
the third choice I'm having trouble with - trying to display all but the ABC
leases.

I realize I have a problem with the syntax, so I've tried every other
possible combination I can think of:

<>"ABC"
"<>'ABC'"
Not "ABC"
"Not ABC"

and nothing seems to work.

I've got to believe there's a way to tell the query to display all leases
except for the ABC leases. Please tell me what I need to enter in the Value
list so I can accomplish this.

Thanks in advance,

Paul

 

[Klatuu]

Can you post the SQL of the query that is pulling this data, please. It
could be a couple of different things, but without seeing the SQL, we can't
give a precise answer.

 

[KARL DEWEY]

You can not use logical operators (<>) in the combo as they will be treated
as text.

 

[Paul]

Ok, here's the SQL:

SELECT DISTINCT tblLease.LeaseID, tblLease.LeaseAgency, tblLease.Address,
tblLease.City, tblLease.County, tblLease.FirmExpiration,
tblLease.ExpirationDate, qry_lease_sqft.SqFt, qry_lease_rent.MaxOfRent AS
Rent, IIf([SqFt]<>0,[Rent]/[SqFt],Null) AS [$/sf],
IIf([ManagingAgency]="General Services, Dept of","DGS",[ManagingAgency]) AS
MgAgency
FROM (tblLease LEFT JOIN qry_lease_rent ON tblLease.LeaseID =
qry_lease_rent.LeaseID) LEFT JOIN qry_lease_sqft ON tblLease.LeaseID =
qry_lease_sqft.LeaseID
WHERE (((tblLease.LeaseAgency) Like [forms]![frm_find_lease]![cboAgency])
AND ((tblLease.Address) Like [forms]![frm_find_lease]![cboAddress]) AND
((tblLease.City) Like [forms]![frm_find_lease]![cboCity]) AND
((tblLease.County) Like [forms]![frm_find_lease]![cboCounty]) AND
((IIf([ManagingAgency]="General Services, Dept of","DGS",[ManagingAgency]))
Like [forms]![frm_find_lease]![cboManagingAgency]))
ORDER BY tblLease.LeaseAgency, tblLease.City;

 

[Klatuu]

You are correct, Karl, but he could use some logic to make it work. That is
why I wanted to see the SQL

 

[John W. Vinson]

I have a combo box in the form header of a continuous form that I'm trying
to use to filter records displayed in the form. I'm trying to use the
following text as the Row Source for the control:

"*";"All leases";"ABC";"ABC leases only";"<>ABC";"Non-ABC leases"

The first two choices, All leases and ABC leases only work fine. That is,
the recordset only displays the appropriate records for those choices. It's
the third choice I'm having trouble with - trying to display all but the ABC
leases.

I realize I have a problem with the syntax, so I've tried every other
possible combination I can think of:

<>"ABC"
"<>'ABC'"
Not "ABC"
"Not ABC"

and nothing seems to work.

I've got to believe there's a way to tell the query to display all leases
except for the ABC leases. Please tell me what I need to enter in the Value
list so I can accomplish this.

Thanks in advance,

Paul

You cannot pass operators such as <> as a parameter.

Try combo box with values

"All leases";"ABC";"Non-ABC leases"

and a criterion of

WHERE [Forms]![YourForm]![yourcombo] = "All Leases"
OR ([Forms]![YourForm]![yourcombo] = "Non-ABC Leases" AND [Lease] <> "ABC")
OR ([Lease] = [Forms]![YourForm]![yourcombo])

 

[Paul]

John,

I've modified the combo box and the WHERE clause of the query according to
your suggestion, but the query displays all records regardless of the
selection I make. Here are the values I'm using:

Row Source: "All leases";"All leases";"DGS";"DGS leases only";"Non-DGS
leases";"Non-DGS leases"

WHERE ((([forms]![frm_find_lease]![cboManagingAgency])="All Leases"))
OR ((([forms]![frm_find_lease]![cboManagingAgency])="Non-DGS leases")
AND ((IIf([ManagingAgency]="General Services, Dept
of","DGS",[ManagingAgency]))<>"DGS"))
OR (((IIf([ManagingAgency]="General Services, Dept
of","DGS",[ManagingAgency]))=[forms]![frm_find_lease]![cboManagingAgency]))

Any suggestion how I can modify this to accomplish my purpose?

Thanks.

Paul

 

[John W. Vinson]

John,

I've modified the combo box and the WHERE clause of the query according to
your suggestion, but the query displays all records regardless of the
selection I make. Here are the values I'm using:

Row Source: "All leases";"All leases";"DGS";"DGS leases only";"Non-DGS
leases";"Non-DGS leases"

Why the duplication? You've got "All Leases" twice, "Non-DGS Leases" twice,
and I would interpret the other two as synonyms as well. And your original
post didn't give any indication about the "General Services, Dept of"
criterion.

Please explain in words what's in your table (relevant to this problem), and
what your purpose in fact is. I'm perplexed by your IIF's.

WHERE ((([forms]![frm_find_lease]![cboManagingAgency])="All Leases"))
OR ((([forms]![frm_find_lease]![cboManagingAgency])="Non-DGS leases")
AND ((IIf([ManagingAgency]="General Services, Dept
of","DGS",[ManagingAgency]))<>"DGS"))
OR (((IIf([ManagingAgency]="General Services, Dept
of","DGS",[ManagingAgency]))=[forms]![frm_find_lease]![cboManagingAgency]))

Any suggestion how I can modify this to accomplish my purpose?

 

[Paul]

John,

I can see how I made it confusing. I should have simplified the two things
you mentioned before using them as examples. I've now done that below, but
first, here are the answers to the two questions you asked.

I originally set up the combo box as a two column combo box, with the
zero-width colum 1 as the bound column. The first component of the Row
Source was the one to display all records, so column 1 had a value of "*",
and column 2 had a value of "All Leases". So when the user opens the
control, he sees "All Leases" as the first choice, and if he selects that
one, the value "*" is applied as the criteria.

After reading your first suggestion, I modified those two components of the
Row Source,

"*";"All Leases

to "All Leases";"All Leases"

Since the two are the same, I realize it would have been better to convert
the two column combo box to one column.

I should also have eliminated the IIf expression from my illustration, but
it does nothing more than convert "General Services, Dept of" to "DGS".

Making these two changes to the Combo Box and the SQL code, my examples
would then be:

Combo Box Row Source: "All leases";"DGS leases only";"Non-DGS leases"

WHERE ((([forms]![frm_find_lease]![cboManagingAgency])="All Leases"))
OR (((tblLease.ManagingAgency)<>"General Services, Dept of")
AND (([forms]![frm_find_lease]![cboManagingAgency])="Non-DGS leases"))
OR
(((tblLease.ManagingAgency)=[forms]![frm_find_lease]![cboManagingAgency]))

This is the WHERE clause that is currently displaying all records,
regardless of the value selected in the combo box.

This query is based on one table, tblLease.
Fields in the table include:
LeaseID,
Address, City, State, Zip, County
BeginDate, ExpirationDate,
LeaseAgency, ManagingAgency.

I have other combo boxes in the form that enable the user to filter by City,
County and LeaseAgency, but I have omitted them from the SQL code to make it
more readable, and also because they are working as desired.

With the case of the ManagingAgency filter, cboManagingAgency, I'm trying to
display three categories of records: All Leases, only those managed by DGS
(General Services, Dept of), and only those not managed by DGS.

I sure appreciate your spending the time to take a look at this.

Paul

John,

I've modified the combo box and the WHERE clause of the query according to
your suggestion, but the query displays all records regardless of the
selection I make. Here are the values I'm using:

Row Source: "All leases";"All leases";"DGS";"DGS leases only";"Non-DGS
leases";"Non-DGS leases"

Why the duplication? You've got "All Leases" twice, "Non-DGS Leases"
twice,
and I would interpret the other two as synonyms as well. And your original
post didn't give any indication about the "General Services, Dept of"
criterion.

Please explain in words what's in your table (relevant to this problem),
and
what your purpose in fact is. I'm perplexed by your IIF's.

WHERE ((([forms]![frm_find_lease]![cboManagingAgency])="All Leases"))
OR ((([forms]![frm_find_lease]![cboManagingAgency])="Non-DGS leases")
AND ((IIf([ManagingAgency]="General Services, Dept
of","DGS",[ManagingAgency]))<>"DGS"))
OR (((IIf([ManagingAgency]="General Services, Dept
of","DGS",[ManagingAgency]))=[forms]![frm_find_lease]![cboManagingAgency]))

Any suggestion how I can modify this to accomplish my purpose?

 

[John W. Vinson]

Making these two changes to the Combo Box and the SQL code, my examples
would then be:

Combo Box Row Source: "All leases";"DGS leases only";"Non-DGS leases"

WHERE ((([forms]![frm_find_lease]![cboManagingAgency])="All Leases"))
OR (((tblLease.ManagingAgency)<>"General Services, Dept of")
AND (([forms]![frm_find_lease]![cboManagingAgency])="Non-DGS leases"))
OR
(((tblLease.ManagingAgency)=[forms]![frm_find_lease]![cboManagingAgency]))

This is the WHERE clause that is currently displaying all records,
regardless of the value selected in the combo box.

This query is based on one table, tblLease.
Fields in the table include:
LeaseID,
Address, City, State, Zip, County
BeginDate, ExpirationDate,
LeaseAgency, ManagingAgency.

I have other combo boxes in the form that enable the user to filter by City,
County and LeaseAgency, but I have omitted them from the SQL code to make it
more readable, and also because they are working as desired.

With the case of the ManagingAgency filter, cboManagingAgency, I'm trying to
display three categories of records: All Leases, only those managed by DGS
(General Services, Dept of), and only those not managed by DGS.

I sure appreciate your spending the time to take a look at this.

The omission of the other criteria is undoubtedly the problem. Things can get
pretty snarky when you have multiple OR criteria, as is necessary in this
case; Microsoft's obsession with adding multiple layers of unnecessary
parentheses just makes matters worse!

Try replacing this part of the WHERE clause with

WHERE (
[forms]![frm_find_lease]![cboManagingAgency]="All Leases")
OR
([forms]![frm_find_lease]![cboManagingAgency])="Non-DGS leases" AND
tblLease.ManagingAgency<>"General Services, Dept of")
OR
([forms]![frm_find_lease]![cboManagingAgency] = "DGS" AND
tblLease.ManagingAgency)="General Services, Dept of")
)
AND <your other criteria>

You can see the logic: the criteria in the outermost parenthesis between the
WHERE and the AND has three branches:

1. If the combo box contains "All Leases" the first clause is TRUE and the
record is retrieved regardless of the value in the field.
2. If it contains "Non-DGS leases" it specifically excludes "General Services,
Dept of".
3. If it contains "DGS" it specifically *selects* "General Services, Dept of".
4. Any other criteria are AND'd with this composite criterion, so that you get
(e.g.) non-DGS leases in the specified date range or whatever.

 

[Paul]

It works!

John, you're a genius!!!

I didn't realize you could designate a form control in a query field and
then apply criteria to it. I can see where once you grasp that concept, the
other logic you suggested in the WHERE clause will produce the desired
records. But setting a query field to the value of a form control - it
would never have occurred to me to do that. When I think of queries, I
always think in terms of building it from fields in tables and other
queries, and sometimes even adding an expression that might not consist of
fields. But putting a form control in there, somehow seems
counterintuitive. I can see that it works, but I guess I need to mull it
over for a while before I can really grasp it conceptually.

Thanks so much for patiently showing me how to solve this problem.

Paul

 

[John W. Vinson]

I didn't realize you could designate a form control in a query field and
then apply criteria to it. I can see where once you grasp that concept, the
other logic you suggested in the WHERE clause will produce the desired
records. But setting a query field to the value of a form control - it
would never have occurred to me to do that. When I think of queries, I
always think in terms of building it from fields in tables and other
queries, and sometimes even adding an expression that might not consist of
fields. But putting a form control in there, somehow seems
counterintuitive. I can see that it works, but I guess I need to mull it
over for a while before I can really grasp it conceptually.

I remember having just the same astonished reaction when I first learned this
trick. It's cool.

The WHERE clause is just a Boolean algebra expression which evaluates to
either TRUE or FALSE. If it's TRUE... the record is retrieved. If it's
FALSE... it's not. Of course normally the truth or falsity of the expression
depends on the value of a field or fields in the record; but, as in this case,
there are times when you make the decision on some other basis.

I've even had occasion to use queries with

WHERE 1 = 1

or

WHERE 1 = 0

if I want to explicitly retrieve all (or no) records.

 

[Paul]

The WHERE clause . . . evaluates to either TRUE or FALSE. If it's TRUE...
the record is retrieved. Of course normally the truth or falsity of the
expression depends on the value of a field or fields in the record; but,
as in this case, there are times when you make the decision on some other
basis.
John W. Vinson [MVP]

So in this case we're using the technique to overcome the fact that the
query can't evaluate the inequality symbol <> in a combo box, and instead,
we hard wire it into the Criteria expression of the query; and the criteria
expressions in the combo box "field" read the selections in the combo box.

Great technique. I wouldn't have thought of that in a million years.

Thanks again, John.

Paul Ponzelli