Custom Field doesn't work with criteria

[y2k]

Sorry the subject isn't great, but I have the following field which calculates the difference (in days) between a field called LastTR (which is a concatnation of three seperate fields and then formatted as date) and today's date.

Diff: DateDiff("d",[LastTR],Date())

It returns the value perfectly, however, I need to eliminate the recors where this value is 3 or less, but when I enter the criteria of >3, it gives the prompt "Enter Parameter Value Last TR". Why is this?

 

[Mile-O]

You have LastTR in your expression, but you say it's asking you for: Last TR

Look through your query for any spaces between Last and TR which are causing the problems.

 

[y2k]

No, that was a typo in my last post, sorry!! I even tried chaning the name altogether in case I had put in some character and just wasn't spotting it but to no avail, it's not working. Is it because LastTR is also a calculated field?

 

[Rich]

Filter the Form/Report or use an Iif statement

 

[y2k]

Thanks, that seems like a good idea. however, the same thing is happening now with that field. It's stopped asking for the LastTR parameter but now, it's looking for the Diff parameter. I created a field called IncludeFlag and stated that IIf([diff]>3,"Y","N") but then when I execute it, I'm getting this problem.

please, can somebody help. This database is scheduled to run every morning and an autoexec macro just runs each query automatically and then subsequently prints each report so I don't think i can use a filter can I?

 

[Mile-O]

NewField: IIf(DateDiff("d",[LastTR],Date())>3, "Y", "N")

 

[y2k]

Good Idea, didn't think of combining the fields. Howeve, I'm back to the same problem. It's asking for the parameter of LastTR. This is driving me nuts, what's wrong with it?

 

[Jon K]

[LastTR] is an alias and therefore cannot be used in the criteria (i.e. the Where Clause) of the query SQL Statement.


Switch your original query to SQL View. In the Where Clause of the SQL Statement, substitute [LastTR] with its expression.

That is change:-
WHERE (((DateDiff("d",[LastTR],Date()))>3));

to:-
WHERE (((DateDiff("d",ExpressionForLastTR,Date()))>3));