拆分存储数值的字段的功能 [英] Split Funtion for a field that stores numeric values
问题描述
我在其他数据库中使用了这个拆分函数来拆分数组，但我似乎无法使用这个数据库。我希望拆分的字段将数据存储为数字值，但在表格中显示名称。我在SharePoint列表中有一个多值
字段，该字段在我的访问数据库中链接，并且此列表在某些字段中有多个名称。以下是我正在使用的功能：
Dim dbs As DAO.Database
Dim rst1作为DAO.Recordset
Dim rst2作为DAO.Recordset
Dim arr As Variant
Dim i As Long
设置dbs = CurrentDb
设置rst1 = dbs.OpenRecordset（" RICE Inventory"，dbOpenForwardOnly）
设置rst2 = dbs.OpenRecordset（"Rice Inventory2"，dbOpenDynaset）
Do While Not Thrst1.EOF是
arr =拆分（rst1。[业主]，"，"）
对于i = 0到UBound（arr）
rst2.AddNew
rst2！ID = rst1！ID
rst2！[状态] = arr（i）
rst2.Update
接下来我是
rst1.MoveNext
循环
rst2.Close
设置rst2 =无结果
rst1.Close
设置rst1 =无结果
设置dbs = Nothing
结束子
数据如下所示：
| ID | |
| 1 | Mohler，James A，Kadar，Laura |
| 2 | Mushill， Kay L，Bowker，Michael J |
| 3 | Kizer， Jane S |
| 4 | Debord， Jason D，Lugge，Joshua D，Sugumar，Senthil Kumar，Moran，John J，London，Dan， Tripathy，Kartikeya，Aziz，Mudassir M，Burns，James |
| 5 | Rackley， Eric C |
| 6 | Steppig， Harlan，Gada，Paul N，Janga，Madhusudhana R |
| 7 | Ellebracht， Michael |
| 8 | |
| 9 | 关闭， Joshua |
| 10 | |
| 11 | Kirkpatrick， Ray R |
| 12 | 科英布拉， 凯斯利 |
| 13 | |
| 14 | |
|
|
|
ID | 业主 |
| 1 | 20732 ; 20733 |
| 2 | |
| 3 | 2811 |
| 4 | 5764; 270; 6075; 9199; 7736; 15804; 17040; 783 |
| 5 | 404 |
| 6 | 10819; 20739; 17642 |
| 7 | 123 |
| 8 | 83 |
| 9 | 19805 |
| 10 | 20749 |
| 11 | 20751 |
| 12 | 18693 |
| 13 | 19011 |
| 14 | 20756 |
| 15 | 13190 |
这两个字段的数据类型是：
ID & NBSP;                     自动编号
业主 数字
我想使用一个单独的表来使用在数字字段上连接到a的数字字段的查询来填充表单具有与数字相关联的实际名称的表格和实际存储的值由";"分隔。 我认为我的主要问题是arr的声明和"i"的声明。作为整数。 我得到一个类型不匹配的箭头消息。
有人可以帮忙吗？
Robert D. Lloyd
一些事情：
Split（）不起作用，因为有问题的字段是多值的（至少我认为是！）。 / p>
此外，如果任何名称在文本中有"，"，则split（）会搞乱。
Dr，Smith，Albert Kallal
也许你的数据非常好，但在上面我们有一个"，"代替"。"因此split（）可能会搞砸。
Split（）不适用于多值列。然而，场景"后面"Access看到一个多值列实际上是一个SEPARATE表。
因此该方法将表"打开"到记录集（你做得正确）。然后对于每个记录，你打开一个另一个reocrdset到ONE列，它包含多值数据。
你的代码看起来像"某样"：
Dim rst1作为DAO.Recordset
Dim rst2作为DAO.Recordset
Dim rstMulti As DAO.Recordset2
Dim arr As Variant
Dim i As Long
设置dbs = CurrentDb为
设置rst1 = dbs.OpenRecordset（" RICE Inventory"，dbOpenForwardOnly）
Set rst2 = dbs.OpenRecordset（" Rice Inventory2"，dbOpenDynaset）
Do While Not Thrst1.EOF是
设置rstMulti = rst1！[业主] .Value
Do While Not ThultiMulti.EOF是
rst2.AddNew
rst2！ID = rst1！ID
rst2！[状态] = rstMulti（0）
rst2.Update
rst1.MoveNext
循环
rstMulti.Close
循环
rst2.Close
设置rst2 =无结果
rst1.Close
设置rst1 =无结果
设置dbs = Nothing
End Sub
问候，
Albert D. Kallal（访问MVP）
埃德蒙顿，加拿大艾伯塔省
I have used this split function in other databases in splitting an array but I can't seem to get it work with this database. The field that I wish to split stores the data in as numeric values but in the table, the names are presented. I have a multivalued field in SharePoint list that is linked in my access database and this list has multiple names in some of the fields. Below is the function that I am using:
Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim arr As Variant
Dim i As Long
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("RICE Inventory", dbOpenForwardOnly)
Set rst2 = dbs.OpenRecordset("Rice Inventory2", dbOpenDynaset)
Do While Not rst1.EOF
arr = Split(rst1.[Business Owner], ",")
For i = 0 To UBound(arr)
rst2.AddNew
rst2!ID = rst1!ID
rst2![Status] = arr(i)
rst2.Update
Next i
rst1.MoveNext
Loop
rst2.Close
Set rst2 = Nothing
rst1.Close
Set rst1 = Nothing
Set dbs = Nothing
End Sub
The data looks like this:
| ID |
Business Owner |
| 1 | Mohler, James A, Kadar, Laura |
| 2 | Mushill, Kay L, Bowker, Michael J |
| 3 | Kizer, Jane S |
| 4 | Debord, Jason D, Lugge, Joshua D, Sugumar, Senthil Kumar, Moran, John J, London, Dan, Tripathy, Kartikeya, Aziz, Mudassir M, Burns, James |
| 5 | Rackley, Eric C |
| 6 | Steppig, Harlan, Gada, Paul N, Janga, Madhusudhana R |
| 7 | Ellebracht, Michael |
| 8 | Bayless, Mike J |
| 9 | Close, Joshua |
| 10 | Loken, Samantha M |
| 11 | Kirkpatrick, Ray R |
| 12 | Coimbra, Kesley |
| 13 | Kraemer, Brian |
| 14 | Mcdavid, Kristine L |
|
ID |
Business Owner |
| 1 | 20732;20733 |
| 2 | 20734;1513 |
| 3 | 2811 |
| 4 | 5764;270;6075;9199;7736;15804;17040;783 |
| 5 | 404 |
| 6 | 10819;20739;17642 |
| 7 | 123 |
| 8 | 83 |
| 9 | 19805 |
| 10 | 20749 |
| 11 | 20751 |
| 12 | 18693 |
| 13 | 19011 |
| 14 | 20756 |
| 15 | 13190 |
Data Type for these two fields are:
ID AutoNumber
Business Owner Number
I wanted to use a separate table to populate a form using a query that is joined on the number field to the number field of a table that has the actual names associated with the numbers and the actual stored values are separated by ";". I think my main problem is the declaring of the arr and the "i" as an integer. I get an arrow message with either type mismatch.
Can someone help?
Robert D. Lloyd
A few things:
Split() will not work since the field in question is multi-valued (at least I think it is!).
Also, if any name ever has a "," in the text, then the split() would mess up.
Dr, Smith, Albert Kallal
Perhaps you data is really nice, but in above we have a "," in place of "." And thus the split() likely would mess up.
Split() will not work on multi-value columns. However "behind" the scenes Access sees a multi-value column as in fact a SEPARATE table.
So the approach is "open" the table to a recordset (you done that correctly). And then for EACH record, you open a another reocrdset to the ONE column that holds the multi-value data.
You code will thus look "something" like:
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rstMulti As DAO.Recordset2
Dim arr As Variant
Dim i As Long
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("RICE Inventory", dbOpenForwardOnly)
Set rst2 = dbs.OpenRecordset("Rice Inventory2", dbOpenDynaset)
Do While Not rst1.EOF
Set rstMulti = rst1![Business Owner].Value
Do While Not rstMulti.EOF
rst2.AddNew
rst2!ID = rst1!ID
rst2![Status] = rstMulti(0)
rst2.Update
rst1.MoveNext
Loop
rstMulti.Close
Loop
rst2.Close
Set rst2 = Nothing
rst1.Close
Set rst1 = Nothing
Set dbs = Nothing
End SubRegards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
这篇关于拆分存储数值的字段的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
