Complicated Extract from Query Field

Need an efficient way to pull LAST NAME, FIRST NAME from query field. All queries return extraneous information (from canned report). I need last name, first name but queries return "TOTAL" Last Name, First Name and Middle Initial with a "." Trick is not all name fields have middle initials so a simple string function won't work (at least not one I could come up with).

Example:
Report returns- TOTAL ABNER, DELEON P.
I need- ABNER, DELEON

Another returned- TOTAL ANAYA CONCILEON, ERICA
I Need- ANAYA CONCILEON, ERICA

Some names have middle initials, some have multiple last names. Very complicated but I know this would be short work to some of the experts on this forum.

thx in advance

Originally Posted by alansidman

Assume your data in Table1, your data is in field "Name1". Created an expression in Field "Name2"

Here is the SQL statement for that query

Please Login or Register  to view this content.

Unfortunately not proficient in SQL. HOw would I do this in a query?

Originally Posted by alansidman

In your query design. Add a new column and create the expression

Please Login or Register  to view this content.

Change the field named Name1 to whatever your field is named.

To see the equivalent SQL statement which should then look similar to what I provided, in the upper left corner under View, click on the dropdown and then SQL. All queries in Access require a SQL statement, MS has given you an interface to use so that you don't need to know SQL, but it will help a lot to understand it.

Thx but this is still giving me the middle initial and period:
ABNER, DELEON P.
Need ABNER, DELEON

I do not know how to find the location of the space AFTER the comma, if could find that I think I could get this.

THx again.

Originally Posted by alansidman

Create a new query using the one above as your record source. Then create a new field and use the following expression

Please Login or Register  to view this content.

Here is the SQL statement for comparative purposes. My previous query was Query2.

Please Login or Register  to view this content.

Change up your query names and field names to your field names and query names.

My apologies. I missed that you wanted to not have the middle initial in your results.

Awesome, worked perfectly. Now I just need to study your verbiage and figured out why it worked.

Thx again.