Update - Conversion Error

[djlysuc]

I have a text column of alpha-numeric fields and some start P123456, P111111, P222222 etc. I want to remove the P from these fields.

I thought the following update query would do this but gives a type conversion error, regardless if the new column to copy them to is type text or number. Can someone help explain what is wrong.

UPDATE 2006 SET D2 = CLng(RIGHT(D1,LEN(D1-1)))
WHERE LEFT(D1,1)="P";

Thanks,
Andy

 

[Jon K]

UPDATE 2006 SET D2 = RIGHT([D1], LEN([D1])-1)
WHERE LEFT(D1,1)="P";
.

 

[djlysuc]

Thanks Jon, I can see my stupid mistake now!