Todays Date in a Query

[Guest]

I have a field named TodaysDate for which I use the following expression:
TodaysDate: Date()... which works fine.
I then have a field named DaysFromClose which uses the expression:
DaysFromClose: DateDiff("d",[Completion Date],[TodaysDate])... which works
fine.

However, when I enter the criteria for DaysFromClose as "<30", I get the
prompt 'Enter Parameter Value' for TodaysDate. It works fine if I enter the
date, but I don't understand why it forgets what it's doing ; - )

I guess the real question is, what am I doing wrong?

 

[Guest]

[TodaysDate] is a calculated field that can be used in a follow-on query
but not in the same query that creates it. You must use the calculation
instead.

 

[Rick B]

Why are you doing this in three steps? Why not just do it in two steps...

DaysFromClose: DateDiff("d",[Completion Date],Date())



Or (EVEN BETTER) in one step...

Under "Completion date" just put...

<Date()-30

 

[Allen Browne]

There are lots of scenarios in Access where you cannot use the alias in the
WHERE clause. To work around it, just repeat the expression instead of using
the alias, e.g.:
DaysFromClose: DateDiff("d",[Completion Date], Date())

An example is where the query has a GROUP BY clause. JET must run the WHERE
clause *before* it can generate the output, so it doens't have the
calculated fields created early enough to use them in the WHERE clause.