文字/備註欄位儲存的數字(如學號「1、10、11、2、3」)若直接排序,會依字元逐位比對導致 11 排在 2 前面。Access 2007 可透過 進階篩選/排序 搭配 Val() 轉數值與 IIf() 處理 Null,實現真正的數值大小排序,無需變更欄位資料類型。
| 學生 | 學號(文字) |
|---|---|
| John | 1 |
| Maria | 10 |
| William | 11 |
| Mary | 12 |
| Jane | 2 |
| Lynn | 3 |
| Jason | 4 |
| Mary | 5 |
字串排序結果:1, 10, 11, 12, 2, 3, 4, 5 — 完全違背數值大小邏輯。
Expr1: IIf([StudentID] Is Null, 0, Val([StudentID]))
IIf([StudentID] Is Null, 0, ...):若欄位為 Null,視為 0 避免排序錯誤。Val([StudentID]):擷取字串開頭的數值部分(Val("123abc") 回傳 123)。| 需求 | 運算式 | 說明 |
|---|---|---|
| 忽略非數值字串(視為 0) | IIf(IsNumeric([Field]), Val([Field]), 0) | IsNumeric 先判斷是否可轉數值 |
| Null 排最後(遞增) | IIf([Field] Is Null, 999999, Val([Field])) | 給極大值推至底部 |
| 支援負數 | Val([Field]) | Val 原生支援負號 |
| 科學記號/小數點 | Val 可處理 | Val("-1.23E2") = -123 |
若可控制資料輸入格式,強制固定寬度:
0000;0;_(四位數)。Format(Number, "0000") → "0001", "0010", "0011", "0002"。代碼格式 A-001, A-10, B-2, A-100,需先依字母再依數值排序:
-- 查詢設計格線
SortKey1: Left([Code], InStr([Code], "-") - 1) -- 字母前綴
SortKey2: Val(Mid([Code], InStr([Code], "-") + 1)) -- 數值部分
排序:SortKey1 遞增, SortKey2 遞增
善用 Val/IIf 運算式在查詢層解決文字欄位數值排序,是 Access 開發者必備的輕量級技巧,避免結構變更風險同時滿足業務排序需求。