Delete words after a comma in a field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jimmy - 07 Apr 2005 11:59 GMT

Hello in a field there is data in which comma seperates words. For example:
John Smith, male, Athens.
What I want is the last word in the right of the last comma (in my example
the word "Athens") to be cuted from that field and be entered in a new
field. Is it possible? Detailed instructions would be appreciated because I
am a newbe.

Thank You
Jimmy

Reply to this Message

Ken Snell [MVP] - 07 Apr 2005 14:09 GMT

If you want to change the data permanently in the table, then you can run an
Update query whose SQL statement is similar to this:

UPDATE YourTableName
SET All3FieldName = Left([All3FieldName], InStrRev([All3FieldName], ",")),
Just1FieldName = Mid([All3FieldName], InStrRev([All3FieldName], ",") + 1);

Signature

Ken Snell
<MS ACCESS MVP>

> Hello in a field there is data in which comma seperates words. For
> example: John Smith, male, Athens.
[quoted text clipped - 5 lines]
> Thank You
> Jimmy

Reply to this Message

Jimmy - 07 Apr 2005 14:19 GMT

Thanks Ken.
Just one question.
Where exactly do I write that?
Sorry for asking but I am new in Access and trying to figure everything out.
Jimmy

> If you want to change the data permanently in the table, then you can run
> an Update query whose SQL statement is similar to this:
[quoted text clipped - 12 lines]
>> Thank You
>> Jimmy

Reply to this Message

Ken Snell [MVP] - 07 Apr 2005 15:45 GMT

Create a new query in design view. If you want to use the SQL statement
directly (of course, you will need to change the names to real names), don't
select a table, and switch to SQL view. Paste the statement into the window
and modify the names.

If you want to design the query in the QBE window, select the desired table,
click the Query Type icon button on the toolbar and select Update, drag the
two fields onto the grid and put in the "Update To:" cells for each the
expressions that I provided:
For the All3FieldName field: Left([All3FieldName],
InStrRev([All3FieldName], ","))
For the Just1FieldName field: Mid([All3FieldName],
InStrRev([All3FieldName], ",") + 1)

Signature

Ken Snell
<MS ACCESS MVP>

> Thanks Ken.
> Just one question.
[quoted text clipped - 21 lines]
>>> Thank You
>>> Jimmy

Reply to this Message

Jimmy - 08 Apr 2005 14:47 GMT

Thanks Ken your help was precious

> Hello in a field there is data in which comma seperates words. For
> example: John Smith, male, Athens.
[quoted text clipped - 5 lines]
> Thank You
> Jimmy

Reply to this Message