解決 Access 專案 (ADP) 與 SQL Server 空值處理衝突

Access 專案 (.adp) 直接連接 SQL Server 時，Access 原生函數 Nz()、IsNull() 在 T-SQL 環境下行為不同甚至失效。本文深入分析空值處理差異，提供對應的 T-SQL 替代方案與最佳實務。

空值的業務意義

空值 ≠ 空字串 ≠ 零值。空值代表「未知」、「尚未決定」、「不適用」。正確處理空值能：

• 提供使用者有意義的顯示 (如「尚未填寫」而非空白)
• 預防下游運算式錯誤 (如 NULL + 100 = NULL 導致報表失真)
• 符合業務規則 (如必填欄位驗證)

Access vs T-SQL 空值函數對照

關鍵陷阱：T-SQL ISNULL() 不是判斷函數

-- Access: IsNull(Field) → True/False
-- T-SQL 錯誤用法:
SELECT ISNULL(MiddleName)  -- 語法錯誤！ISNULL 需 2 參數

-- T-SQL 正確判斷空值:
SELECT CASE WHEN MiddleName IS NULL THEN 1 ELSE 0 END AS HasMiddleName
-- 或
SELECT IIF(MiddleName IS NULL, 1, 0) AS HasMiddleName  -- SQL 2012+

ADP 環境下的空值處理策略

1. 檢視表/預存程序中統一處理 (推薦)

CREATE VIEW vw_EmployeeDisplay AS
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    ISNULL(MiddleName, '') AS MiddleName,           -- 空值轉空字串
    ISNULL(Title, '未指定職稱') AS Title,           -- 空值轉預設文字
    COALESCE(HomePhone, MobilePhone, '無連絡電話') AS ContactPhone,  -- 多欄位備援
    CASE WHEN BirthDate IS NULL THEN '未提供' ELSE CONVERT(VARCHAR(10), BirthDate, 111) END AS BirthDateDisplay
FROM Employees

2. 存取專案表單/報表控制項屬性

• ControlSource：綁定檢視表已處理欄位 (如 MiddleNameDisplay)
• DefaultValue：新增記錄時預設值 (如 =Date()、="N/A")
• ValidationRule：Is Not Null 強制必填
• InputMask：格式化輸入減少空值產生

3. ADP 中無法使用的 Access 函數清單

Nz()          →  改用 ISNULL() / COALESCE()
IsNull()      →  改用 IS NULL / IIF(... IS NULL, ...)
DLookup()     →  改用預存程序/檢視表 JOIN
DCount()      →  改用 COUNT(*) 配合 WHERE
DMax/DMin     →  改用 MAX()/MIN() 配合 WHERE
DateDiff()    →  改用 DATEDIFF() (參數順序不同!)
Format()      →  改用 CONVERT()/FORMAT() (SQL 2012+)
IIf()         →  改用 CASE WHEN / IIF() (SQL 2012+)

空值處理最佳實務清單

1. 資料庫層面：關鍵業務欄位設 NOT NULL + DEFAULT，源頭阻擋空值
2. 檢視表層面：建立「顯示用檢視表」統一處理空值轉換，應用層只查檢視表
3. 應用層面：ADP 表單/報表綁定檢視表，不直接綁定基底資料表
4. 報表層面：使用 IIF(ISNULL(...), '顯示文字', ...) 或 Format() 控制輸出
5. 測試驗證：單元測試覆蓋空值邊界條件 (全 NULL、部分 NULL、無 NULL)

進階：資料行層級空值語意標記

-- 擴充屬性記錄空值業務含義
EXEC sp_addextendedproperty 
    @name = N'NullMeaning',
    @value = N'尚未進行健檢',
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'Patients',
    @level2type = N'COLUMN', @level2name = 'LastCheckupDate'

-- 查詢中繼資料
SELECT 
    OBJECT_NAME(c.object_id) AS TableName,
    c.name AS ColumnName,
    ep.value AS NullMeaning
FROM sys.columns c
JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE ep.name = 'NullMeaning'

結語

ADP 開發的核心心法：將空值處理下推至資料庫層 (檢視表/預存程序)，前端只負責呈現。善用 ISNULL、COALESCE、CASE、IIF 建立乾淨的顯示層，避免 Access 與 T-SQL 函數語意衝突導致的難除錯問題。