避免Access和SQL Server的空值衝突

 在連結到一個SQL Server資料庫的Access項目（即.adp檔案）中，你平時經常使用的Access函數通常是不支援的。但大多數時候，都可換用SQL Server支援的一個Transact-SQL（T-SQL）函數。一個特例是Access的空值（Null）函數Nz和IsNull；它們的功能相似，但並不是access和T-SQL都能支援它們。本文要討論在什麼時候以及如何使用這兩個函數來處理空值。

 

空值的重要性

空值是完全合法的資料庫錄入項，它們能提供的資訊可能超過你的想象。空值指明一個值是未知的，但這並不是說該值不可接受或者不存在。空值表明一個值尚未確定，而且除非確定了合適的值，否則該值將保持未知狀態，也就是為一個空值。
開發人員的職責是判斷空值可能在什麼時候出現，並針對性地處理那些值。一個方案是在資料輸入時拒絕空值，從而完全迴避它們。但這樣一來，你的資料將完全禁止空值，所以該方案的缺點在於，在你建立一條記錄時，所有資料都必須是已知的。遺憾的是，對於大多數人來說，這個方案都不現實。以醫院急診室的資料庫系統為例，是不是因為資料錄入員不知道一個病人的中間名，就拒絕為其建立一條記錄呢？所以，對空值進行妥善處理可提供更靈活的方案，因為即使資料錄入員當時不知道所有資料，也能成功地建立記錄。

認識到空值無法避免之後，你的職責就是正確處理它們，確保它們不會導致錯誤。正確處理空值有兩方面的好處：
•可與使用者共用有意義的資訊。
•可提前捕捉空值，避免空值在後續的運算式中返回錯誤。

access 中的空值

access提供大量工具來操縱空值：
•Is Null——在條件運算式和SQL WHERE子句中使用Is Null。
•IsNull——將IsNull用於Visual Basic for Applications（VBA）。
•Nz——遇到空值時，用Nz返回除Null之外的其他值。

以上函數在本質上存在細微差異。Is Null和IsNull捕捉空值；而Nz函數處理空值。換言之，如果想返回的不是實際儲存的空值，就使用Nz。例如，你可顯示一個簡單字串（例如"N/A"），讓使用者知道資料對於目前記錄來說“不可用”。另外，當空值在運算式中使用之前，你可以捕捉空值，因為空白值經常會返回錯誤。

通過連結資料表或者Access項目來使用Access和SQL Server時，會產生一個問題，因為access和SQL Server不是用相同的函數來判斷空值。因此，你必須知道要使用什麼函數，以及在什麼時候使用。

在.mdb檔案中使用Nz

大多數access使用者和開發人員都熟悉Nz函數，它在遇到空值時返回除空值之外的其他值。該函數的形式如下，其中variant代表Variant資料類型，而valueifnull是一個選擇性參數，它負責在variant為空白值時提供真正的傳回值。

Nz(variant[, valueifnull])

如省略valueifnull，Nz傳回值0或一個零長度字串（""），具體由variant的資料類型決定。

圖A的查詢展示了如何使用Nz及其結果。該查詢基於access樣本資料庫Northwind中的Employees表。
圖A

兩個Nz函數都返回字串資料型別，因為Region的資料類型為Text

B所示，兩個Nz函數都返回字串值，而不是空值，但具體的字串是不同的。第一個運算式返回字串"N/A"來取代空值：

WithOptional: Nz([Region],"N/A")

通過返回一個實際的字串或者值，可為使用者提供比空值更多的資訊。字串"N/A"明確指出：目前記錄沒有合適的值。相反，如返回空值，那麼一個空白的欄位可能使不熟悉的使用者產生誤解。

圖B

Nz返回一個實際的值，而不是容易使人產生誤解的空值
第二個運算式返回零長度的字串來取代空值：

WithoutOptional: Nz([Region])

雖然這樣為使用者提供的資訊較少，但卻有效防止了在後續的運算式中使用空值，從而避免資料庫出錯。圖C顯示的新運算式證明了這一點。

圖C

運算式可能不能像預期的那樣處理空值
兩個運算式都直接或間接依賴於Region欄位中的值。第一個運算式直接引用Region欄位：

RegionString: IIf([Region]="","N/A",[Region])

只看錶達式，你可能認為結果記錄集的每個欄位中都應該出現一個字串。圖D展示了實際結果，它和你設想的不符，並不是每個欄位都包含一個字串。事實上，只有在欄位包含零長度字串（""）的前提下，運算式才會返回字串"N/A"。我們知道，這些看似空白的欄位包含空值，而不是零長度的字串。但是，錯誤是可以理解的，也是能避免的。

圖D

遇到空值時，用Nz傳回值而不是錯誤
下一個運算式也引用了Region值，只是通過Nz函數的結果來間接地引用：

WithoutOptionalString: IIf([WithoutOptional]="","N/A",[WithoutOptional])

這樣一來，任何空值都可用一個更確切的值來表示，並在後續任何錶達式中進行求值。D所示，運算式在處理空白Region欄位時沒什麼問題。只有“未預計到”或者“未知”的空值才會導致問題。

Nz在項目中無法工作

有人以為Access項目也支援Nz函數，但實情並非如此。記住，項目中的表是真正的SQL Server表。要證明這一點非常簡單，只需使用Access配套提供的示範項目NorthwindCS.adp。以Employees表的一個簡單視圖為基礎，並像前面在Microsoft資料庫（.mdb）檔案中那樣使用Nz函數，E所示。試圖運行該查詢時，access會返回一個錯誤。

圖E

access項目不支援Nz函數

在access項目中使用Nz

在項目中不能使用Nz，而應換用Transact SQL的IsNull函數。該函數的作用與Nz相似，它的形式如下：

IsNull(expression, valueifnull)

兩個參數都是必需的，IsNull結果值的類型由expression決定。如果valueifnull的列資料類型與expression的資料類型衝突，SQL Server會在你試圖輸入運算式時返回一個錯誤。例如，假如expression基於一個數值列，而valueifnull是一個字串，兩個參數就會產生衝突。由於SQL Server無法解決衝突，所以會拒絕運算式。

為瞭解決上例（圖E）的問題，可將Nz替換成IsNull運算式，F所示：

IsNull(Region, N/A)

結果與圖B相同（在WithOptional欄位中）。

圖F

在access項目中用IsNull代替Nz

避免空值錯誤

access和SQL Server用不同的函數來處理空值（分別是Nz和IsNull）。正確處理空值可防止資料庫出現執行階段錯誤和產生無效資料。不要迴避空值，只需正確地處理它們。