On Tue, 8 Sep 2009 14:18:26 -0700, Brad Hodges
wrote:

John,

Thank you very much. I'm not sure what this creates. I have an admin table
(query) which is derived from an allstudent table. How do I eliminate the
status column yet allow the query to function? What is "NZ" in your example?
Is this a pseudoname? Thanks again.

Is your admin table a table, or a query? They are NOT THE SAME THING: a table
is actual stored data on disk, and a query is a volatile derived object
extracting data from a table (or tables). My suggestion is a Query which will
include a field named Status, containing either "active" or "history",
depending on the value of the date field.

NZ() is a built in function in Access that converts a NULL value to its second
argument, so

IIF(NZ([Graduation date], Date()) = Date(), "Active", "History")

will use today's date in the comparison if [Graduation date] is NULL; this
will ensure that a record with no graduation date specified is shown as Active
(along with those with a graduation date in the future). Graduation dates that
are in the past will be shown as "History".

--

John W. Vinson [MVP]