removing a digit from string

[wilsation]

Hi, I have a field called PropertyID which stores a unique 13 digit number, the 10th digit is always a 3. I want to remove the 3 to leave a 12 digit number.
I've tried running an Update query using Replace([PropertyID],"3","",10,1) but no joy, what am I doing wrong?

Thanks in advance

 

[vbaInet]

Welcome to AWF!

The functions you need are Left(), Instr() and Mid()

 

[wilsation]

Hi, I found the Left,Instr,Mid functions when googling the problem but couldn't figure out how to get them to do what I wanted

 

[vbaInet]

Let's see what you wrote.

And if you're absolutely sure that it's always going to be in the 10th position then you only need Left() and Right().

 

[wilsation]

Hi, yes the I always need to remove the 10th digit and it's always a 3, I don't have any of the code I tried yesterday as I deleted it when it didn't work. Whenever I tried using Left() it just returned everything after the 10th digit.

 

[vbaInet]

Again, let's see what you wrote. It's no help telling me that it didn't work without showing me what didn't work.

 

[wilsation]

This is what I tried using the Replace function

Replace([PropertyID],"3","",10,1)

 

[vbaInet]

I've seen that already, I'm talking about the Left() function you said you tried.

 

[wilsation]

Ok, Left([PropertyID],9) returns characters 1-9

Right([PropertyID],3) returns 11-13

Can I concatenate the two strings which will return everything except the 10th character? Something like Left(([PropertyID],9),Right([PropertyID],3))

 

[vbaInet]

You use an ampersand (&)

 

[wilsation]

Thanks for your help, this works

(Left([PropertyID],9) & Right([PropertyID],3))