Use DATE in query expression

[Joe M.]

I am trying to create a expression field in a query that compares a date from
a table with today's date but getting an error. My expression is:

Sched2: Switch([date]>[basic finish],"Expired",IsNull([basic finish]),"Not
Scheduled",[basic finish]>1/1/2001,"")

If I remove the first part of the expression containing [date] then it works
as below:
Sched2: Switch(IsNull([basic finish]),"Not Scheduled",[basic
finish]>1/1/2001,"")

I suppose my problem is with how I'm using DATE. Can someone help?

Many thanks,
Joe M.

 

[Klatuu]

There is more than one problem. First, if what you have coded as [date] is
what you are trying to use to return the date, it is incorrect. If it is a
field name, change the name. I have seen the use of the word date as a name
cause problems even when in brackets. Also, it is necessary to enclose date
values in #. Try this:

Sched2: Switch(Date()>[basic finish],"Expired",IsNull([basic finish]),"Not
Scheduled",[basic finish]>#1/1/2001#,"")