criteria for dealing with dates

[Jonathan A. Cheyne]

Hi,
I am pretty new to this database malarkey, I'm building a database recording
the details of every computer in the school where I work, including a field
for the date each machine was physically checked out for erroneous software,
physical damage, creative redecoration etc. etc. and I am trying to create a
query which will give the number of days since this last checkup (or, if
there is no data, return the message "URGENT CHECKUP REQUIRED"). without
showing machines that have been checked in the last (arbitrarily) 60 days.

 

[John Spencer]

SQL Statement would look something like the following:

SELECT *
, IIF(SomeDateField is Null,"URGENT",DateDiff("d",SomeDateField,Date()) as HowLong
FROM SomeTable
WHERE SomeDateField < DateAdd("d",-60,Date()) Or SomeDateField is Null

In query design view (query grid)
-- Add your table
-- Select the fields you want to see - if you don't want to see your date
field, add it anyway and uncheck the SHOW button.
-- Add a calculated field by entering
HowLong: IIF([SomeDateField] is Null, "URGENT",
DateDiff("d",[SomeDateField],Date())
-- Add criteria under [SomeDateField] on two criteria lines
Is Null
< DateAdd("d",-60,Date())


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County