如何将“全名"字段拆分为“名字"，“姓氏"和“中间名首字母"? [英] How to split Full Name field into First Name, Last Name and Middle Initial?

问题描述

我有一个表，其中有一个名为PATRN NAME的字段，该字段是用First_Name, Last_Name M.I.

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

示例:

史密斯，詹姆斯M

Smith, James M

琼斯，克里斯·J.

安德森，温迪L

如何将该字段分为3个不同的字段，分别称为FIRST_NAME，LAST_NAME和MI?我尝试运行查询LAST_NAME: [PATRN NAME]" & ", "来获取姓氏，但没有得到任何结果.我并没有设计这个表，所以我意识到没有一个单独的字段名来创建一个完整的字段名并不明智.我只是负责修复它.

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.

推荐答案

请考虑是否可以弯曲

Consider whether you can bend the Split Function to 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

您不能在查询中直接使用Split().但是，您可以构建一个或多个用户定义的函数，然后从查询中调用UDF.

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.

与需要结合其他功能的查询相比，这种方法可以使查询更简单:InStr()，Mid()，Right()等.但是，UDF意味着查询只能在Access应用程序中进行会议;如果您需要从Access(.Net，VBScript，PHP等)外部运行的查询，则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.

我建议您澄清一下，是要在每次查询数据时提取FIRST_NAME，LAST_NAME和MI，还是在提取一次之后是否将这些值分别存储在表中.那应该会影响您选择的方法.如果您将它们拆分一次并存储，则可以使用VBA过程代替查询.

I suggest you clarify whether your intention is to extract FIRST_NAME, LAST_NAME, and MI every 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.

还决定在中间的首字母琼斯，克里斯·J"(Jones，Chris J.)之后使用可选点进行处理.保留在MI中还是将其丢弃?

Also decide what should happen with the optional dot after the middle initial, "Jones, Chris J.". Keep it in MI or discard it?

这篇关于如何将“全名"字段拆分为“名字"，“姓氏"和“中间名首字母"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！