Delete words after a comma in a field

  • Thread starter Thread starter Jimmy
  • Start date Start date
J

Jimmy

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
 
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);
 
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

Ken Snell said:
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);

--

Ken Snell
<MS ACCESS MVP>

Jimmy said:
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
Click to expand...
Click to expand...
 
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)

--

Ken Snell
<MS ACCESS MVP>


Jimmy said:
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

Ken Snell said:
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);

--

Ken Snell
<MS ACCESS MVP>

Jimmy said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top