Aceess 2003 Query using NOT operator?

A database I have inherited has field 'Status'. Data is entered in to this
field from a range values in a combo box
"planning/design";"data collection";"data input";"data analysis";"report
preparation";"action plan preparation";"awaiting summary";"project
complete";"project postponed";"project cancelled";"unknown";"long term"

I want to create a query that will return all records 'excluding' "project
complete";"project postponed";"project cancelled" values.

I've played with the NOT operator but think I'm probabably way off mark.

Can anyone suggest a way forward.

Thank you
Trevor

Trevor Aiston wrote:
A database I have inherited has field 'Status'. Data is entered in to
this field from a range values in a combo box
"planning/design";"data collection";"data input";"data
analysis";"report preparation";"action plan preparation";"awaiting
summary";"project complete";"project postponed";"project
cancelled";"unknown";"long term"

I want to create a query that will return all records 'excluding'
"project complete";"project postponed";"project cancelled" values.

I've played with the NOT operator but think I'm probabably way off
mark.

Can anyone suggest a way forward.

Thank you
Trevor

WHERE Status Not In("project complete", "project postponed", "project
cancelled")

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Trevor Aiston wrote:
A database I have inherited has field 'Status'. Data is entered in to
this field from a range values in a combo box
"planning/design";"data collection";"data input";"data
analysis";"report preparation";"action plan preparation";"awaiting
summary";"project complete";"project postponed";"project
cancelled";"unknown";"long term"

I want to create a query that will return all records 'excluding'
"project complete";"project postponed";"project cancelled" values.

I've played with the NOT operator but think I'm probabably way off
mark.

Can anyone suggest a way forward.

The easiest way is:

WHERE NOT [status] IN ("project complete","project postponed","project
cancelled")

This can be rewritten as:

WHERE [status] "project complete" AND [status] "project postponed" AND
[status] "project cancelled"

I prefer the easy way


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Trevor Aiston wrote:
A database I have inherited has field 'Status'. Data is entered in to
this field from a range values in a combo box
"planning/design";"data collection";"data input";"data
analysis";"report preparation";"action plan preparation";"awaiting
summary";"project complete";"project postponed";"project
cancelled";"unknown";"long term"

I want to create a query that will return all records 'excluding'
"project complete";"project postponed";"project cancelled" values.

I've played with the NOT operator but think I'm probabably way off
mark.

Can anyone suggest a way forward.

PS. A more robust, data-driven approach, especially useful when you have to
write multiple queries that need to exclude the same set of status values,
is to create a table, perhaps called "StatusValuesToExclude", enter the list
of status values you wish to exclude, and do a left join to it, filtering
out the records where the staus from the "exclude" table is not null. Like
this:

select ...
from tablename as t left join StatusValuesToExclude as e
on t.[status]=e.[status]
where e.[status] is not null

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

"Bob Barrows [MVP]" wrote in message
...
Trevor Aiston wrote:
A database I have inherited has field 'Status'. Data is entered in to
this field from a range values in a combo box
"planning/design";"data collection";"data input";"data
analysis";"report preparation";"action plan preparation";"awaiting
summary";"project complete";"project postponed";"project
cancelled";"unknown";"long term"

I want to create a query that will return all records 'excluding'
"project complete";"project postponed";"project cancelled" values.

I've played with the NOT operator but think I'm probabably way off
mark.

Can anyone suggest a way forward.

PS. A more robust, data-driven approach, especially useful when you have
to write multiple queries that need to exclude the same set of status
values, is to create a table, perhaps called "StatusValuesToExclude",
enter the list of status values you wish to exclude, and do a left join to
it, filtering out the records where the staus from the "exclude" table is
not null. Like this:

select ...
from tablename as t left join StatusValuesToExclude as e
on t.[status]=e.[status]
where e.[status] is not null

Sigh ... I was in too much of a rush. This of course, is backwards. To
exclude the statuses in StatusValuesToExclude you want to only include the
results where e.[status] contains null:

.... where e.[status] is null