sqlms-accesscalculated-columnsdatediff

SQL Issue: calculated query column won't recognize day interval in DateDiff?

For some reason I can't get my calculated query column to recognize a day interval. Every time I test run the form that displays the query results, a parameter pop-up asks me to enter the value of d.

This is my SQL column formula:

ExceedsTime: (IIf(IsNull([CloseOutDate]) And (DateDiff(["d"],[SurveyDate],Now())>45),1,0))

Basically I need to count any results where the Survey has not been completed within the 45-day deadline.

If we're running past the deadline, return a 1 value toward the total count. If we're still within the 45-day window, return a 0 value.

SurveyDate = Day the survey was conducted
CloseOutDate = Day the survey is completed.

I've tried it anyway my peanut brain can reword it.

Is there any way I can make it recognize day as any interval and not a parameter value?

Solution

  • I'm not entirely sure why the SQL insists on putting the d interval in brackets, so I've just decided to split the expression.

    For the calculated query field, I've shortened it to only give me count totals:

    ActiveDays: (Abs(IIf(IsNull([CloseOutDate]), ([SurveyDate]-Date()))))

    Then I had to calculate the average of those totals on the form:

    =Avg([ActiveDays])