CODEWITHSUNDEEP
sqlms-accesscalculated-columnsdatediff
Melissa Hersh
Melissa Hersh

Reputation: 1

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?

Upvotes: 0

Views: 110

Answers (2)

Melissa Hersh
Melissa Hersh

Reputation: 1

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])

Upvotes: 0

ValNik
ValNik

Reputation: 5926

DateDiff defined as

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

where interval is string.

Part Description
interval Required. String expression that is the interval of time you use to calculate the difference between date1 and date2.

In your case interval="d"

(DateDiff("d",[SurveyDate],Now())

If you write ["d"], this interpreted by Access as variable or column name "d".

Upvotes: 1

Related Questions