Ener Parameter Value box opening when I don't want it to

[David Ball]

Hi,

I have a query that is Grouped By a field called KPI. I have a Date field in the query, SortDates, that I have named LatestDates by setting it up like LatestDates: SortDates
I have then placed MAX in the Total row to show the latest date for each KPI.
This works well but I want to set up a calculated field to show the variance to another date field, TargetDate.
The calculated field is Variance: ([TargetDate]-[LatestDates])
It works but every time I run it I get the Enter Parameter Value box opening up. I just select OK, without entering anything, and the query runs and returns what I need.
How can I stop the Enter Parameter Value box from opening?
Thanks very much
Dave

 

[arnelgp]

you can directly put sortdate there:

Variance:TargetDate-SortDates

 

[David Ball]

Hi arnelgp, it then shows me all the dates, not just the Max, which is what I need

Thanks very much

 

[arnelgp]

Variance:TargetDate-CDate(DMax("SortDates","yourTable" "KPI" = [KPI]))