referencing results in the same query,

[Homegrownandy]

I have a query named NoticeQ_1day that displays 3 dates.

old_date, New_Date, When

I want the criteria for old date to be greater than when.

If I use >[NoticeQ_1day].[When] It comes up with an input box asking for this value.
if I use >[When] it displays all results even the ones before when.

What am I doing wrong?

[pbaldy]

I would expect the second to work. In SQL view:

WHERE old_date > When

That assumes they are fields in a table being returned by the query, not calculated in the query. It also assumes they are date/time values, not text.

[Homegrownandy]

Sorry for the late reply, I've been busy on other things Paul. They are being calculated on the query. How would you suggest I proceed?

I can re-write the question if you need more info.

Andy.

[John_G]

Try running the query with no criteria, just the three values.

Are there any rows where [Old_Date] actually is > [when] ?

This might be stating the obvious, but bear in mind that When comparing dates, [Old_Date] > [when] means [Old_Date] is more recent than [when]. Is that what you want?

[pbaldy]

If they are calculated in the query you can't use the alias. Try repeating the calculation. In SQL view:

WHERE CalculationForOldDate > CalculationForWhen

[Homegrownandy]

Hello John. That is correct.

Its a scheduled start date so its always AFTER when.

When = date of change.

Paul - Thanks for that suggestion. I will look at this tomorrow.

So if we change a scheduled start date it always a date in the near future.