Break Name Field in Format Last Name, Middle Name First Name into Last Name Only

[TINMAN]

I would like to break the Name Field in my Access Database in the Format Last Name, First Name Middle Name into the Last Name Only.

I can do this in Microsoft Excel using the Left Function. Is there any comparable function I can use in a Query or Form to break out the Last Name.

I do not know how to use Visual Basic.



Thanks for your help.

[June7]

Access also has Left() as well as other string manipulation functions. Works in query, textbox, VBA.

[ArviLaanemets]

Yes, Left() will do it in Access too. But for both Excel and Access, it will work only for cases where there are no gaps in Last Name. As an easiest example of contrary, when marring, in some countries women can keep her Last Name, and add husband's Last Name (at least I think it is possible, as I remember having seen some examples).

[TINMAN]

I tried using this expression from Excel in Microsoft Access and it did not work.

=LEFT(A3,SEARCH(" ",A3)-2)

only instead of A3 I used the Name in the Format LAST NAME, FIRST NAME MIDDLE NAME



Access said it did not have a SEARCH function.

What do you recommend?

Thanks for your response.

[moke123]

?left("LAST NAME, FIRST NAME MIDDLE NAME",instr(1,"LAST NAME, FIRST NAME MIDDLE NAME",",")-1)

Use the left() along with Instr(). Assumes there is always a comma after last name.

[TINMAN]

Thanks for you reply.

I ended up using this expression:

The formula used below was used to convert a Name in the Format

Last Name, First Name Middle Name

To Last Name Only

Trim(Left([Name],InStr([Name],”,”)-1))

I am attaching a pdf file which shows the Expression Builder used.

I have to give credit to Data Pig on You Tube as this is where I found this expression.

Thanks for your help.

[TINMAN]

Thanks,

I ended using the Left String Manipulation Function shown below:

Trim(Left([Name],InStr([Name],”,”)-1))

[TINMAN]

Thanks,

I ended up using the expression below:

Trim(Left([Name],InStr([Name],”,”)-1))