将字符串数据拆分为多列访问 [英] Split string data into multiple columns access

问题描述

我有一个包含多个服务代码的列，我希望将它们拆分成不同的列，以便在Access中进行计数。一行示例如下所示：

I have a column with multiple service codes that I am looking to split into different columns for tallying purposes in Access. An example of one line looks like this:

$= ** ;= AA AC BB1 CA5 CC4 CC5 CC6 CC7 CC9 CD1 CI CR D0 D1 D2 D3 D9 F> F> FA7 FA9 FB1 HA6 KJ P* P? PW PZ QG V4 WJ 1D 2O

目标是创建AA列，AC列等计算哪个服务代码用于哪一行。服务代码的每一行也与其他代码唯一不同。有什么想法吗？我对宏和SQL查询也很熟悉。

The goal is to create an AA column, an AC column etc to tally which service code is being used for what row. Also each line of service codes is uniquely different from the others. Any thoughts? I'm kinda familiar with macros and SQL queries too.

这是我到目前为止的内容：

Here's what I have so far:

Sub splitColumn()

Dim rst As DAO.Recordset
Dim arr() As String
Dim i As Integer

Set rst = CurrentDb.OpenRecordset("SELECT * FROM _Raw")
arr = split(rst("Serv Cde String"), ",")

For i = 0 To UBound(arr)
  '/do something here
Next i

End Sub

谢谢

丽贝卡

推荐答案

分隔符似乎是错误的：

Sub splitColumn()

    Dim rst As DAO.Recordset
    Dim arr As Variant
    Dim i As Integer

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM _Raw")
    arr = Split(rst("Serv Cde String").Value, " ")

    For i = LBound(arr) To UBound(arr)
      '/do something here
      Debug.Print i, arr(i)
    Next

    Set rst = Nothing

End Sub

这篇关于将字符串数据拆分为多列访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！