使用IIF语句结合DateDiff时，True分支未生效问题排查

Let's figure out why your current subquery isn't working for employees who haven't retired/left yet, and fix it up quickly.

The Core Problem #

In Access SQL, checking if a field is NULL with = NULL doesn't work the way you'd expect. NULL represents a missing value, so comparing anything to NULL (even another NULL) returns an unknown result—not True. That's why your IIF statement never triggers the Now() calculation when [Date Retired/Left] is empty.

The Fixed Subquery #

Use IS NULL instead of = NULL to properly test for missing values. Here's the corrected version:

(SELECT IIF([Date Retired/Left] IS NULL, DateDiff("yyyy", [Date Employed], Now()), DateDiff("yyyy", [Date Employed], [Date Retired/Left])) 
 FROM [Employment History] 
 WHERE [Employment History].EmployeeID = [Training History].EmployeeID) AS [Time Employed]

Optional: More Precise Tenure Calculation #

If you want to account for partial years (instead of just full calendar years), you can adjust the logic to calculate based on days and round to a decimal:

(SELECT IIF([Date Retired/Left] IS NULL, 
            Round(DateDiff("d", [Date Employed], Now())/365.25, 1), 
            Round(DateDiff("d", [Date Employed], [Date Retired/Left])/365.25, 1)) 
 FROM [Employment History] 
 WHERE [Employment History].EmployeeID = [Training History].EmployeeID) AS [Time Employed]

This will return values like 4.7 instead of just 4, giving a more accurate view of an employee's tenure.

内容的提问来源于stack exchange，提问作者James May