Use DATE in query expression

J

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.
 
K

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#,"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

extract date then search by the date 6
Date expression in Query 4
Date Criteria in Query and getting error message 1
If before today's date insert Expired 4
Date Query 2
use expression as field name in query 2
Expression in query 3
Generating system date in Query 2

Top