Query criteria

  • Thread starter Thread starter Nona
  • Start date Start date
N

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!
 
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


ruralguy via AccessMonster.com said:
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!
Click to expand...
Click to expand...
 
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


ruralguy via AccessMonster.com said:
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"
Click to expand...
[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!
Click to expand...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
Back
Top