Field x contains *field y^

[Valery]

Hi All! Been a while; soooo nice to be back! Hope someone as an « easy » solution! Internet search provided,

Here is an example of what is needed:

In Table A, the field client = Doe, John (no matter how many words, the first name(s) is/are always after the comma followed by a space.

I would like, from that client field, to extract first & Last names to the two fields I created as : FirstName and LastName.

Thank you – help is much appreciated.

 

[Galaxiom]

Left([client], Instr([client], ",") - 1)) AS  LastName

Mid([client], Instr([client], ",") + 2)) AS FirstNames

 

[Galaxiom]

BTW It is often good to apply Trim() to the results to remove any leading or trailing spaces that might have been included.

Another variant in situations where there might not be a comma included in every record (eg a business name among the data).

Left([client] & ",", Instr([client] & ",", ",") - 1)) AS  LastName

Concatenating the comma prevents the error that would otherwise be thrown when there is no comma and Instr() returns 0.

 

[Valery]

Hi - thank you for your help.

Not sure where the "trim" is but tried:

[FONT=&quot]Left([client] & ",", Instr([client] & ",", ",") - 1)) AS LastName[/FONT]


Got error, "the expression has too many parenthesis"

Remove one and got "invalid syntax"

Added one and got "... function containing the wrong arguments"

Help! THANK YOU

 

[Galaxiom]

Gets confusing with all the commas

[B][COLOR=seagreen]Trim([/COLOR][COLOR=blue]Left([/COLOR][/B][client] & ","[COLOR=blue][B],[/B][/COLOR] [COLOR=darkorchid][B]Instr([/B][/COLOR][client] & ","[COLOR=purple][B],[/B][/COLOR] ","[COLOR=darkorchid][B])[/B][/COLOR] - 1[B][COLOR=blue])[/COLOR][COLOR=seagreen])[/COLOR][/B] AS LastName

Edit: The bolding I tried screws up during the editing.

 

[Valery]

Ok... I still get error messages like invalid syntax... Can you help me by being very explicit as I have tried this in a select query, an update query... as a field and as a criteria to a field.

So I can only suspect that the coding is not working due to my ignorance. Also I am working with Access 2016. Any chance this also a factor?

Could you, please, create a small database with a 2-field table with 3 records and 1 query with your coding?

THANK YOU

 

[isladogs]

Galaxiom's code is for 2 separate fields in a select query
It will work in any version of Access.

However, I would recommend you change your table design to use separate fields for last name and first name. It's much easier to join these fields when needed than to deal with possible complications trying to split them

 

[Galaxiom]

I would recommend you change your table design to use separate fields for last name and first name.

I assumed Valery was in the process of doing this and why the code was required.

Attached example including refinements.

 

[isladogs]

Just read post 1 again and that is indeed the purpose of this exercise.