MS Access Update Query

I am trying to figure out if 开发者_JAVA百科an update query is really what I need. I have a field called account_numbers, and each entry consists of a 3 digit number prefaced by the letter "M".

Account number

M001
M002
M003

And it goes all the way up to 999. All I want to do is remove the "M" from each account number. I looked over the Microsoft tutorials for an update query, and it looks like I may need something else. Can someone please tell me the easiest way to do this?

Thanks,

---

try this:

UPDATE account_numbers
SET AccountNumber = Replace([AccountNumber],"M","")

EDIT: take a backup first :D

---

If you want to use an update query, you can use the Replace() or Mid() functions as suggested in the other answers. However, since you want to keep only the 3 right-most characters, my impulse would be to reach for the Right() function. Here's a sample from the Immediate Window:

? Right("M001", 3)
001

So, in a query, it could look something like this:

UPDATE account_numbers
SET AccountNumber = Right(AccountNumber , 3)

If there may be AccountNumber values which don't match the pattern of "M" plus 3 digits, you can add a WHERE clause to ignore them in the update:

WHERE AccountNumber Like "M###"

---

If this is once-off, the easiest would be to just find and replace on the column.

---

If you can be absolutely sure that the only M in the account numbers is the one at the beginning, then Davide Piras' answer (replacing "M" by an empty string) is just fine.

However, if there were more "M"s in the account numbers and you just want to get rid of the first one (like: MKLMN --> KLMN), then replacing wouldn't work and you'd have to "cut off" only the first character:

UPDATE account_numbers 
SET AccountNumber = Mid([AccountNumber],2);