Query criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nona - 04 May 2008 17:30 GMT

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!

Signature

Nona

Reply to this Message

ruralguy - 04 May 2008 17:44 GMT

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
[quoted text clipped - 4 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!

Signature

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

Reply to this Message

Nona - 04 May 2008 17:55 GMT

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?

Signature

Nona

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

Reply to this Message

ruralguy - 04 May 2008 18:05 GMT

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:
>
[quoted text clipped - 18 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!

Signature

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

Reply to this Message

Nona - 04 May 2008 18:40 GMT

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.

Signature

Nona

> You need to use AND rather than OR.
> WHERE (((tblAuths.AuthsStatus)<>"Expired" AND (tblAuths.AuthsStatus)
[quoted text clipped - 22 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!

Reply to this Message

ruralguy - 04 May 2008 20:54 GMT

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.
[quoted text clipped - 4 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!

Signature

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

Reply to this Message