edit table data

X

Xazn

I have a table with text (numbers and letters) that vary
from 6 to 20 characters long. I want to add a '-'3
places from the end on every character in the one
column. An example is CSL2414105 would be changed to
CSL2414-105. Not sure if it is easier in SQL, Access, or
Excel. Any help would be appreciated.
 
J

John Vinson

I have a table with text (numbers and letters) that vary
from 6 to 20 characters long. I want to add a '-'3
places from the end on every character in the one
column. An example is CSL2414105 would be changed to
CSL2414-105. Not sure if it is easier in SQL, Access, or
Excel. Any help would be appreciated.
Click to expand...

An Update query in Access would do this pretty readily: base an Update
query on the table, and update the field (I'll call it MyField, use
your own fieldname of course) to

Left([MyField], Len([MyField] - 3)) & "-" & Right([MyField], 3)

John W. Vinson[MVP]
 
X

xazn

That worked on most but not on the items with letters in
the middle. 1000H03 turned up as #ERROR and the column
is text type. It does work on the data that is only
numbers. This is whats entered.

Item_no : 'CSL' & Left([Item #],Len([Item #]-2)) & "-" &
Right([Item #],2)
 
J

John Vinson

That worked on most but not on the items with letters in
the middle. 1000H03 turned up as #ERROR and the column
is text type. It does work on the data that is only
numbers. This is whats entered.

Item_no : 'CSL' & Left([Item #],Len([Item #]-2)) & "-" &
Right([Item #],2)
Click to expand...

Could you post the complete SQL of this query? Are you running an
Append query, an Update query, or what?

John W. Vinson[MVP]
 
Top