数据排序处理 Access数据库系统功能讲解 VBA代码编程实例
Function 排序去重(ByVal datatext As String, ByVal splittext As String, ByVal sortype As String, ByVal typetext As String) As String
Dim data_array
data_array = Split(datatext, splittext)
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 拆分数据表"
DoCmd.RunSQL del_sql
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("拆分数据表", dbOpenTable)
Dim i
With add_rs
For i = 0 To UBound(data_array)
.AddNew
!数据名称.Value = data_array(i)
.Update
Next i
.Close
End With
Set add_rs = Nothing
'生成处理结果
Dim data_text As String
Dim search_rs As DAO.Recordset
Dim search_sql As String
If typetext = "排序去重" Then
If sortype = "升序" Then
search_sql = "Select * From 筛选不重复数据查询" & " order by 数据名称 ASC"
Else
search_sql = "Select * From 筛选不重复数据查询" & " order by 数据名称 DESC"
End If
End If
If typetext = "排序" Then
If sortype = "升序" Then
search_sql = "Select * From 拆分数据表" & " order by 数据名称 ASC"
Else
search_sql = "Select * From 拆分数据表" & " order by 数据名称 DESC"
End If
End If
Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)
Do While search_rs.EOF = False
If data_text <> "" Then
data_text = data_text & splittext & search_rs!数据名称.Value
Else
data_text = search_rs!数据名称.Value
End If
search_rs.MoveNext
Loop
search_rs.Close
Set search_rs = Nothing
排序去重 = data_text
End Function
Private Sub Command降序_Click()
If Me.原数据 <> "" Then
If Me.分隔符 <> "" Then
Me.处理结果 = 排序去重(Me.原数据, Me.分隔符, "降序", "排序")
Else
MsgBox "分隔符不能为空"
End If
Else
Me.处理结果 = ""
End If
End Sub
Private Sub Command降序去重_Click()
If Me.原数据 <> "" Then
If Me.分隔符 <> "" Then
Me.处理结果 = 排序去重(Me.原数据, Me.分隔符, "降序", "排序去重")
Else
MsgBox "分隔符不能为空"
End If
Else
Me.处理结果 = ""
End If
End Sub
Private Sub Command升序_Click()
If Me.原数据 <> "" Then
If Me.分隔符 <> "" Then
Me.处理结果 = 排序去重(Me.原数据, Me.分隔符, "升序", "排序")
Else
MsgBox "分隔符不能为空"
End If
Else
Me.处理结果 = ""
End If
End Sub
Private Sub Command升序去重_Click()
If Me.原数据 <> "" Then
If Me.分隔符 <> "" Then
Me.处理结果 = 排序去重(Me.原数据, Me.分隔符, "升序", "排序去重")
Else
MsgBox "分隔符不能为空"
End If
Else
Me.处理结果 = ""
End If
End Sub
