By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Date Query using the NOT operator (Access 97)

P: n/a
I am developing a weed management database for the preserve I work on. One
of the queries I'm working on will allow the user to run a query and see which weed
patches have NOT been treated in the current calendar year.

This query involves two tables. The first contains the static information
about the weed patches. The second contains Treatment information. The
tables look something like this

Static Table fields:
Weed_ID (integer)
Species (text)

Treatment Table Fields:
Treatment number (autonumber)
Weed_ID (integer)
Treatment_date (date/time)
Treatment_type (text)

The tables are joined by Weed_ID

The query needs to parse the treatment table and return [static table].weed_id
and [static table].species where there is NO treatment record(s) with
treatment_date values in the current calendar year.

Access 97 seems to handle the NOT operator in a non standard fashion, that or
my syntax is really messed up.

Any help is greatly appreciated.

Scott Morford
Pine Butte Swamp Preserve
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Scott Morford <sm******@visi.com> wrote in message news:<3f**********************@newsreader.visi.com >...
I am developing a weed management database for the preserve I work on. One
of the queries I'm working on will allow the user to run a query and see which weed
patches have NOT been treated in the current calendar year.

This query involves two tables. The first contains the static information
about the weed patches. The second contains Treatment information. The
tables look something like this

Static Table fields:
Weed_ID (integer)
Species (text)

Treatment Table Fields:
Treatment number (autonumber)
Weed_ID (integer)
Treatment_date (date/time)
Treatment_type (text)

The tables are joined by Weed_ID

The query needs to parse the treatment table and return [static table].weed_id
and [static table].species where there is NO treatment record(s) with
treatment_date values in the current calendar year.


I think NOT is a red herring... you want an outer join.

SELECT ST.Weed_ID, ST.Species
FROM ST LEFT JOIN TT ON ST.WeedID=TT.WeedID
WHERE TT.Treatment_date NOT BETWEEN Date() AND
DateAdd("yyyy",Date(),-1);

or something like that... but you *definitely* need an outer join.
The part I'm not sure of is the exact syntax of the Between clause.

HTH,
Village Idiot
Nov 12 '05 #2

P: n/a
Scott Morford <sm******@visi.com> wrote in message news:<3f**********************@newsreader.visi.com >...
I am developing a weed management database for the preserve I work on. One
of the queries I'm working on will allow the user to run a query and see which weed
patches have NOT been treated in the current calendar year.

This query involves two tables. The first contains the static information
about the weed patches. The second contains Treatment information. The
tables look something like this

Static Table fields:
Weed_ID (integer)
Species (text)

Treatment Table Fields:
Treatment number (autonumber)
Weed_ID (integer)
Treatment_date (date/time)
Treatment_type (text)

The tables are joined by Weed_ID

The query needs to parse the treatment table and return [static table].weed_id
and [static table].species where there is NO treatment record(s) with
treatment_date values in the current calendar year.

Access 97 seems to handle the NOT operator in a non standard fashion, that or
my syntax is really messed up.

Any help is greatly appreciated.

Scott Morford
Pine Butte Swamp Preserve


Scott,]

Her are 2 solutions:

one using a left outer join and a group by clause (a:
SELECT [Static Table].weed_id, [Static Table].species
FROM [Static Table] LEFT JOIN Treatment ON [Static Table].weed_id =
Treatment.weed_id
WHERE (((Year([treatment_date]))<>Year(Date()) Or
(Year([treatment_date])) Is Null))
GROUP BY [Static Table].weed_id, [Static Table].species;
and a more elegant one with a subquery:
SELECT [Static Table].weed_id, [Static Table].species
FROM [Static Table]
WHERE ((([Static Table].weed_id) Not In (select [weed_id] from
treatment where year([treatment_date]) = year(date()))));
Both are made in the query-editor of Access in QBE mode. In tha last
one the SQL subquery expression 'Not in ...' must be written manually
in the criteria box.

Marc
Nov 12 '05 #3

P: n/a
I would stay away from a NOT IN subquery if possible. a LEFT JOIN is
*much* faster... <borrowing Kent Brockman's 2 cents...>
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.