vba 将 ADO 记录集文本字段排序为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/156084/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sorting ADO recordset text field as numeric
提问by Mark Nold
Using VBA i have a set of functions that return an ADODB.Recordsetwhere all the columns as adVarChar. Unfortunately this means numerics get sorted as text. So 1,7,16,22 becomes 1,16,22,7
使用 VBA 我有一组函数返回一个ADODB.Recordsetwhere 所有列作为adVarChar. 不幸的是，这意味着数字被排序为文本。所以 1,7,16,22 变成 1,16,22,7
Is there any methods that can sort numerics as text columns without resorting to changing the type of the column?
是否有任何方法可以将数字作为文本列进行排序而无需更改列的类型？
Sub TestSortVarChar()
Dim strBefore, strAfter As String
Dim r As ADODB.RecordSet
Set r = New ADODB.RecordSet
r.Fields.Append "ID", adVarChar, 100
r.Fields.Append "Field1", adVarChar, 100
r.Open
r.AddNew
r.Fields("ID") = "1"
r.Fields("Field1") = "A"
r.AddNew
r.Fields("ID") = "7"
r.Fields("Field1") = "B"
r.AddNew
r.Fields("ID") = "16"
r.Fields("Field1") = "C"
r.AddNew
r.Fields("ID") = "22"
r.Fields("Field1") = "D"
r.MoveFirst
Do Until r.EOF
strBefore = strBefore & r.Fields("ID") & " " & r.Fields("Field1") & vbCrLf
r.MoveNext
Loop
r.Sort = "[ID] ASC"
r.MoveFirst
Do Until r.EOF
strAfter = strAfter & r.Fields("ID") & " " & r.Fields("Field1") & vbCrLf
r.MoveNext
Loop
MsgBox strBefore & vbCrLf & vbCrLf & strAfter
End Sub
NB: I am using Project 2003 and Excel 2003 and referencing Microsoft ActiveX DataObject 2.8 Library
注意：我正在使用 Project 2003 和 Excel 2003 并引用Microsoft ActiveX DataObject 2.8 库
采纳答案by Mitch Wheat
Left pad with Zeros with at least as many as maximum number digits. e.g.
左填充零，至少与最大数字位数一样多。例如
0001 0010 0022 1000
0001 0010 0022 1000
You can use Right$() to accomplish this.
您可以使用 Right$() 来完成此操作。
回答by Chris OC
Use the Val() function to sort numerically on a text column. Example:
使用 Val() 函数对文本列进行数字排序。例子：
SELECT ID, Field1
FROM tablename
ORDER BY Val(Field1);
