Prompt for "Parameter" when criteria is entered.

[loki1049]

I have a query that takes a date value from 1 table say [AreaDate] and a day value from another table say [DaysInExcess]. It then creates a field that adds the two values to get a new value of "NewDate":

Example:

NewDate : [AreaDate]+[DaysInExcess]

I then create another field called "YearDate" as below:

YearDate : Year([NewDate])

The query runs fine, until I put a value in the criteria of the "YearDate" field, then it prompts me to enter a parameter for [NewDate]. Anyone know how to avoid this? I don't want a suggestion to create a field called:

Year([AreaDate]+[DaysInExcess])

Unless of course this is the only solution, mainly because this example is a highly simplified version of my problem to illustrate whats going wrong. I guess the question is can you not use results from expressions in other expressions that make use of criteria??

 

[pbaldy]

The problem is that the WHERE clause is evaluated before the SELECT clause, so the alias is unknown at that time. One solution is the one you described, another is to leave this query without criteria and base a second query on this one.

 

[loki1049]

I figured as much, thanks for the timely response though!