拆分存储数值的字段的功能

本文介绍了拆分存储数值的字段的功能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我在其他数据库中使用了这个拆分函数来拆分数组，但我似乎无法使用这个数据库。我希望拆分的字段将数据存储为数字值，但在表格中显示名称。我在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 Bayless，  Mike J 9 关闭，  Joshua 10 Loken，  Samantha M 11 Kirkpatrick，  Ray R 12 科英布拉， 凯斯利 13 Kraemer，  Brian 14 Mcdavid，  Kristine L ID 业主 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 这两个字段的数据类型是： 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 SubThe data looks like this:IDBusiness Owner1Mohler, James   A, Kadar, Laura2Mushill,   Kay L, Bowker, Michael J3Kizer,   Jane S4Debord,  Jason D, Lugge, Joshua D, Sugumar, Senthil Kumar, Moran, John J, London, Dan,   Tripathy, Kartikeya, Aziz, Mudassir M, Burns, James5Rackley,   Eric C6Steppig,   Harlan, Gada, Paul N, Janga, Madhusudhana R7Ellebracht,   Michael8Bayless,   Mike J9Close,   Joshua10Loken,   Samantha M11Kirkpatrick,   Ray R12Coimbra,   Kesley13Kraemer,   Brian14Mcdavid,   Kristine LIDBusiness Owner120732;20733220734;15133281145764;270;6075;9199;7736;15804;17040;7835404610819;20739;176427123883919805102074911207511218693131901114207561513190Data Type for these two fields are:ID                        AutoNumberBusiness Owner    NumberI 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 KallalPerhaps 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 这篇关于拆分存储数值的字段的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！