如何拆分名称字段具有不正确的数据？ [英] How to split a Name Field that has incorrect data?

问题描述

我有一个字段名为PATRN_NAME这是设置了一个将First_Name表，姓氏MI

例如：

·史密斯，詹姆斯中号

·琼斯，克里斯学家

我想现场分解成名字，姓氏和MI领域。我刚才问了这个问题，有人帮我用斯普利特（）来获得LAST_NAME领域。然而，当我试图使用Split（）函数FIRST_NAME这是行不通的，因为现场有一个不按字段的名称约定，而是有如下记载：城图书馆 - GW或捐赠纽约市。

在我的code遇到这些类型的名称，它抛出错误下标越界对，我现在用的第一个！FIRST_NAME =斯普利特（修剪（斯普利特（RST！PATRN_NAME行，）（ 1）），）（0）。我怎样才能让我的code运行仅在遵循标准命名约定的大部分领域？

数据

 功能Change_Name（）

昏暗的星展银行作为DAO.DATABASE
昏暗首先作为DAO.Recordset

设置DBS = CurrentDb
设置RST = dbs.OpenRecordset（活动赞助人，dbOpenDynaset）

rst.MoveFirst

做，而不是rst.EOF

    rst.Edit
    RST！姓氏=斯普利特（RST！PATRN_NAME，，）（0）
    RST！FIRST_NAME =斯普利特（修剪（斯普利特（RST！PATRN_NAME，）（1）），）（0）
    rst.Update
    rst.MoveNext

循环
端功能
 

解决方案

您有两个分裂：一次逗号;另一个空间。因此声明两个字符串数组来保存从这些分裂的结果。

暗淡astrComma（）作为字符串 昏暗的astrSpace（）作为字符串

那么我想它会使用这些阵列在你的循环更加简单。

rst.Edit astrComma =斯普利特（RST！PATRN_NAME，，） 如果UBound函数（astrComma）＆GT; 0然后     这意味着PATRN_NAME至少包含一个逗号，     所以假设LAST_NAME是第一个逗号之前的一切     RST！姓氏= astrComma（0）     预计FIRST_NAME present在astrComma的第二个成员     astrSpace =拆分（修剪（astrComma（1）），） 其他     MSGBOX＆AMP中没有LAST_NAME; RST！PATRN_NAME 结束如果 如果UBound函数（astrSpace）＆GT; = 0然后     你可能还需要检查这是否是一个空     字符串，然后存放好;确实实地让     空字符串？     RST！FIRST_NAME = astrSpace（0） 其他     MSGBOX＆AMP中没有FIRST_NAME; RST！PATRN_NAME 结束如果 rst.Update

I have a table with a field called PATRN_NAME which is set up with First_Name, Last_Name M.I.

Examples:

Smith, James M

Jones, Chris J.

I am trying to break up the field into FIRST_NAME, LAST_NAME and MI fields. I just asked a question about this and someone helped me use Split() to get the LAST_NAME field. However, when I try to use the Split() function for the FIRST_NAME it does not work because the field has records that do not follow the name convention of the field and instead are as follows: "Town Library - GW" or "Donation from New York City".

When my code encounters these types of names it throws the error "Subscript out of range" on the line where I am using rst!FIRST_NAME = Split(Trim(Split(rst!PATRN_NAME, ",")(1)), " ")(0). How can I make my code run only on the data that follows the standard name convention for most of the field?

Function Change_Name()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Active Patrons", dbOpenDynaset)

rst.MoveFirst

Do While Not rst.EOF

    rst.Edit
    rst!LAST_NAME = Split(rst!PATRN_NAME, ",")(0)
    rst!FIRST_NAME = Split(Trim(Split(rst!PATRN_NAME, ",")(1)), " ")(0)
    rst.Update
    rst.MoveNext

Loop
End Function

解决方案

You have two splits: once for comma; another for space. So declare two string arrays to hold the results from those splits.

Dim astrComma() As String
Dim astrSpace() As String

Then I think it will be simpler using those arrays in your loop.

rst.Edit
astrComma = Split(rst!PATRN_NAME, ",")
If UBound(astrComma) > 0 Then
    ' this means PATRN_NAME contains at least one comma,
    ' so assume LAST_NAME is everything before first comma
    rst!LAST_NAME = astrComma(0)
    ' expect FIRST_NAME present in second member of astrComma
    astrSpace = Split(Trim(astrComma(1)), " ")
Else
    MsgBox "no LAST_NAME in " & rst!PATRN_NAME
End If

If UBound(astrSpace) >= 0 Then
    ' you may also want to check whether this is an empty
    ' string before you store it; does the field allow 
    ' empty strings?
    rst!FIRST_NAME = astrSpace(0)
Else
    MsgBox "no FIRST_NAME  in " & rst!PATRN_NAME
End If
rst.Update

这篇关于如何拆分名称字段具有不正确的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！