Query criteria

[Nona]

I have created a new database that contains more than 2000 authorizations for
service. The edit form is supposed to show only Approved or Pending
authorizations, (not denied or expired) but one single record shows up as
Denied. The query behind the form is a select query and lists the criteria as
<> denied or expired. Other denied authorizations do not show up – just this
one. I have deleted this record and re-entered it, but this one record still
shows up in both the query and the edit form. Any clues as to why it insists
on showing up uninvited? Thanks for your assistance!

 

[ruralguy via AccessMonster.com]

You need to expand the criteria and be explicit:
[YourField] <> "Denied" AND [YourField] <> "Expired"

 

[Nona]

The field name is AuthsStatus and it is specific in the SQL as follows:

SELECT tblAuths.AuthNo, qryConsumers.ConsumerNo1, tblAuths.ConsumerNo2,
qryConsumers.CLN, qryConsumers.CFN, tblAuths.AuthStart, tblAuths.AuthEnd,
tblAuths.AuthUnitsApproved, qryConsumers.TLFN, qryConsumers.TLLN,
tblAuths.AuthSubmitted, tblAuths.AuthReceived, tblAuths.UnitsUsed,
tblAuths.ServiceCode, tblAuths.CostCtr, tblAuths.AuthsStatus,
tblAuths.Source, qryConsumers.ClientStatus, tblAuths.Comment, tblAuths.TarNo,
tblAuths.ReasonDenied
FROM qryConsumers INNER JOIN tblAuths ON qryConsumers.ConsumerNo1 =
tblAuths.ConsumerNo2
WHERE (((tblAuths.AuthsStatus)<>"Expired" Or
(tblAuths.AuthsStatus)<>"Denied"))
ORDER BY tblAuths.AuthNo, qryConsumers.CLN, qryConsumers.CFN;

Is this what you mean or am I clueless?

--
Nona

You need to expand the criteria and be explicit:
[YourField] <> "Denied" AND [YourField] <> "Expired"

I have created a new database that contains more than 2000 authorizations for
service. The edit form is supposed to show only Approved or Pending
authorizations, (not denied or expired) but one single record shows up as
Denied. The query behind the form is a select query and lists the criteria as
<> denied or expired. Other denied authorizations do not show up – just this
one. I have deleted this record and re-entered it, but this one record still
shows up in both the query and the edit form. Any clues as to why it insists
on showing up uninvited? Thanks for your assistance!

 

[ruralguy via AccessMonster.com]

You need to use AND rather than OR.
WHERE (((tblAuths.AuthsStatus)<>"Expired" AND (tblAuths.AuthsStatus)
<>"Denied"))

The field name is AuthsStatus and it is specific in the SQL as follows:

SELECT tblAuths.AuthNo, qryConsumers.ConsumerNo1, tblAuths.ConsumerNo2,
qryConsumers.CLN, qryConsumers.CFN, tblAuths.AuthStart, tblAuths.AuthEnd,
tblAuths.AuthUnitsApproved, qryConsumers.TLFN, qryConsumers.TLLN,
tblAuths.AuthSubmitted, tblAuths.AuthReceived, tblAuths.UnitsUsed,
tblAuths.ServiceCode, tblAuths.CostCtr, tblAuths.AuthsStatus,
tblAuths.Source, qryConsumers.ClientStatus, tblAuths.Comment, tblAuths.TarNo,
tblAuths.ReasonDenied
FROM qryConsumers INNER JOIN tblAuths ON qryConsumers.ConsumerNo1 =
tblAuths.ConsumerNo2
WHERE (((tblAuths.AuthsStatus)<>"Expired" Or
(tblAuths.AuthsStatus)<>"Denied"))
ORDER BY tblAuths.AuthNo, qryConsumers.CLN, qryConsumers.CFN;

Is this what you mean or am I clueless?

You need to expand the criteria and be explicit:
[YourField] <> "Denied" AND [YourField] <> "Expired"

[quoted text clipped - 7 lines]

 

[Nona]

That worked! Thanks very much. This is an awesome service. I have learned
quite a bit of detail just by reading the blogs. Thanks for your help.

--
Nona

You need to use AND rather than OR.
WHERE (((tblAuths.AuthsStatus)<>"Expired" AND (tblAuths.AuthsStatus)
<>"Denied"))

The field name is AuthsStatus and it is specific in the SQL as follows:

SELECT tblAuths.AuthNo, qryConsumers.ConsumerNo1, tblAuths.ConsumerNo2,
qryConsumers.CLN, qryConsumers.CFN, tblAuths.AuthStart, tblAuths.AuthEnd,
tblAuths.AuthUnitsApproved, qryConsumers.TLFN, qryConsumers.TLLN,
tblAuths.AuthSubmitted, tblAuths.AuthReceived, tblAuths.UnitsUsed,
tblAuths.ServiceCode, tblAuths.CostCtr, tblAuths.AuthsStatus,
tblAuths.Source, qryConsumers.ClientStatus, tblAuths.Comment, tblAuths.TarNo,
tblAuths.ReasonDenied
FROM qryConsumers INNER JOIN tblAuths ON qryConsumers.ConsumerNo1 =
tblAuths.ConsumerNo2
WHERE (((tblAuths.AuthsStatus)<>"Expired" Or
(tblAuths.AuthsStatus)<>"Denied"))
ORDER BY tblAuths.AuthNo, qryConsumers.CLN, qryConsumers.CFN;

Is this what you mean or am I clueless?

You need to expand the criteria and be explicit:
[YourField] <> "Denied" AND [YourField] <> "Expired"

[quoted text clipped - 7 lines]

shows up in both the query and the edit form. Any clues as to why it insists
on showing up uninvited? Thanks for your assistance!

 

[ruralguy via AccessMonster.com]

Glad I could help Nona! Enjoy your project.

That worked! Thanks very much. This is an awesome service. I have learned
quite a bit of detail just by reading the blogs. Thanks for your help.

You need to use AND rather than OR.
WHERE (((tblAuths.AuthsStatus)<>"Expired" AND (tblAuths.AuthsStatus)

[quoted text clipped - 22 lines]