more dashes

  • Thread starter Thread starter Xazn
  • Start date Start date
X

Xazn

I think I am close but don't know what to change.
I have 2 problems that are almost the same.

1) I am trying to update a field (4 to 18 characters
long) in Access that will add a dash 2 place from the end
and add CSL to the front. If the field has 1000H03 I
want it to be CSL1000H-03.

This is what I tried:

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

1000H03 turned up as #ERROR. If its number only then
00259603 turns up as CSL0025960-03 but should be
CSL002596-03 and 2414105 should be CSL2414-105 but turns
up as CSL24141-105


2) I also have a field (4 to 12 characters long)
that I want to add a dash 3 place from the end and add
CAM to the front. If the field is XT5000B508 I want it
to be CAMXT5000B-508
 
Xazn said:
I think I am close but don't know what to change.
I have 2 problems that are almost the same.

1) I am trying to update a field (4 to 18 characters
long) in Access that will add a dash 2 place from the end
and add CSL to the front. If the field has 1000H03 I
want it to be CSL1000H-03.

This is what I tried:

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

1000H03 turned up as #ERROR. If its number only then
00259603 turns up as CSL0025960-03 but should be
CSL002596-03 and 2414105 should be CSL2414-105 but turns
up as CSL24141-105
Click to expand...

You have a parenthesis in the wrong place. You may as well be
consistent about the quote characters you use, too, though I don't think
it makes a difference in SQL. Try this:

Item_no : "CSL" & Left([Item #], Len([Item #]) - 2) & "-" &
Right([Item #], 2 )
2) I also have a field (4 to 12 characters long)
that I want to add a dash 3 place from the end and add
CAM to the front. If the field is XT5000B508 I want it
to be CAMXT5000B-508
Click to expand...

The same sort of expression should work, but use 3 instead of 2, and
"CAM" instead of "CSL".
 
Back
Top