vba 如何将全名字段拆分为名字、姓氏和中间名首字母？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18107309/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to split Full Name field into First Name, Last Name and Middle Initial?
提问by nedstark179
I have a table with a field called PATRN NAMEwhich is set up with First_Name, Last_Name M.I.
我有一个带有字段的表，该字段PATRN NAME设置为First_Name, Last_Name M.I.
Examples:
例子：
Smith, James M
Jones, Chris J.
Anderson, Wendy L
史密斯，詹姆斯 M
琼斯，克里斯·J。
安德森，温迪 L
How can I break up this field into 3 different fields called FIRST_NAME, LAST_NAME, and MI ? I tried running a query LAST_NAME: [PATRN NAME]" & ", "to get just the last name but it didn't get any results. I didn't design this table by the way so I realize it wasn't smart to make a full field name without individual field names; I'm just in charge of fixing it.
如何将此字段分解为 FIRST_NAME、LAST_NAME 和 MI 的 3 个不同字段？我尝试运行查询LAST_NAME: [PATRN NAME]" & ", "以获取姓氏，但没有得到任何结果。顺便说一下，我并没有设计这个表，所以我意识到制作一个没有单独字段名称的完整字段名称是不明智的；我只是负责修理。
回答by HansUp
Consider whether you can bend the Split Functionto your will.
考虑您是否可以根据自己的意愿弯曲拆分功能。
Here is an example Immediate window session.
这是一个立即窗口会话示例。
PATRN_NAME = "Smith, James M"
? PATRN_NAME
Smith, James M
? Split(PATRN_NAME, ",")(0)
Smith
? Trim(Split(PATRN_NAME, ",")(1))
James M
? Split(Trim(Split(PATRN_NAME, ",")(1)), " ")(0)
James
? Split(Trim(Split(PATRN_NAME, ",")(1)), " ")(1)
M
You can't use Split()in a query directly. However you could build one or more user-defined functions and call the UDF(s) from a query.
您不能Split()直接在查询中使用。但是，您可以构建一个或多个用户定义的函数并从查询中调用 UDF。
That approach could make for a simpler query than one which requires a combination of other functions: InStr(), Mid(), Right(), etc. However, a UDF means the query can only work from within an Access application session; if you need a query which runs from outside Access (.Net, VBScript, PHP, etc.), the UDF will not be available.
这种方法可以使超过一个简单的查询需要的其他功能的组合：InStr()，Mid()，Right()等。然而，一个UDF意味着查询可以从Access应用程序会话中唯一的工作; 如果您需要从 Access 外部（.Net、VBScript、PHP 等）运行的查询，UDF 将不可用。
I suggest you clarify whether your intention is to extract FIRST_NAME, LAST_NAME, and MIevery time you query the data, or whether you will store those values separately in the table after you extract them once. That should influence the approach you choose. If you will split them out once and store, you could use a VBA procedure instead of a query.
我建议您澄清您的意图是在每次查询数据时提取FIRST_NAME、LAST_NAME和MI，还是在提取一次后将这些值单独存储在表中。这应该会影响您选择的方法。如果将它们拆分一次并存储，则可以使用 VBA 过程而不是查询。
Also decide what should happen with the optional dot after the middle initial, "Jones, Chris J.". Keep it in MIor discard it?
还要决定中间首字母“Jones, Chris J”后面的可选点应该发生什么。. 保留它MI还是丢弃它？
回答by mr.Reband
Try this:
尝试这个：
For last name: LAST_NAME: Mid(PATRN_NAME,1,InStr(PATRN_NAME,',')-1)
对于姓氏： LAST_NAME: Mid(PATRN_NAME,1,InStr(PATRN_NAME,',')-1)
For first name: FIRST_NAME: Mid(PATRN_NAME,InStr(PATRN_NAME,',')+2)
对于名字： FIRST_NAME: Mid(PATRN_NAME,InStr(PATRN_NAME,',')+2)
For MI: MI: Mid(Mid(PATRN_NAME,InStr(PATRN_NAME,',')+2),InStr(Mid(PATRN_NAME,InStr(PATRN_NAME,',')+2),' '),3)
对于 MI： MI: Mid(Mid(PATRN_NAME,InStr(PATRN_NAME,',')+2),InStr(Mid(PATRN_NAME,InStr(PATRN_NAME,',')+2),' '),3)
回答by KRich
Try this:
尝试这个：
UPDATE Tbl_Master_Working SET Tbl_Master_Working.[Last Name] = Mid([NAME],1,InStr([NAME],',')-1), Tbl_Master_Working.[First Name] = Mid([NAME],InStr([NAME],',')+2);
