Update Query - Replace first two Characters

[GavZ]

Firstly I have been searching the forums for ages for help with this but have been unsuccessful.

Basically I have a column in a table that i want to update the first two characters if they are "44" with a "0".

I currently have an Update Query with Left([PhoneNo],2) ="44" in the field box but dont know how to replace the returned records with a "0"

Please help! - Thanks

 

[jdraw]

Are you creating an Update query?
Can you show us exactly what you are trying?

 

[GavZ]

yes an update query but im not sure how to replace the 44 with a 0

i know this is obviously wrong but its what i have so far -

 

[MSAccessRookie]

It looks like you have a start towards getting where you want to be, but need to try to use the Replace() Function to help you get the rest of the way. Visit the Link below, and get back to us after you have tried.

http://www.techonthenet.com/access/functions/string/replace.php

 

[jdraw]

Gavz,

You need to update the PhoneNo field. The left("PhoneNo',2) is just for the criteria,

I have attached an update jpg to show similar situation (using my table Animal).

In my case I am inserting "0" and the current AName starting in position 3 for those records where the leftmost 2 characters are "sp".

In effect, I am replacing the "sp" with "0".

 

[GavZ]

sort of but not really

i used Replace([PhoneNo],"44","0")

it replaces all of the 44 no matter where in the field but using a select query it doesnt update the original record, that why i was trying to use the Update query because it sounded like it was the right one.

if i try and put it simply i would say: If the FIRST 2 Characters in the field are "44" then replace them with "0". The Field is called PhoneNo if that helps.

If theres an eaiser way than a Query let me know!

Cheers for your help!

 

[MSAccessRookie]

sort of but not really

i used Replace([PhoneNo],"44","0")

it replaces all of the 44 no matter where in the field but using a select query it doesnt update the original record, that why i was trying to use the Update query because it sounded like it was the right one.

if i try and put it simply i would say: If the FIRST 2 Characters in the field are "44" then replace them with "0". The Field is called PhoneNo if that helps.

If theres an eaiser way than a Query let me know!

Cheers for your help!

It looks like you forgot to take advantage of the optional fourth parameter. Refer to the previous Link for Details.

[B][B]Replace("alphabet", "a", "e") returns "elphebet" (NOT what you are looking for)[/B][/B]
[B]BUT[/B]
[B]Replace("alphabet", "a", "e", 1) returns "elphabet" (What you are looking for)[/B]

 

[Kiwiman]

Howzit

Here is anupdate query that should work, I hope anyway.

update Yourtable Set Yourtable.Yourfield = "0"&right([Yourtable].[Yourfield],len([Yourtable].[Yourfield])-2) where ((left([Yourtable]![Yourfield],2)=44"))

 

[Galaxiom]

This is not a difficult problem and Replace is definitely not part of the most appropriate solution even with the fourth argument. We don't want the first instance replaced. We want the first two characters replaced.

Kiwiman is onto it but it could be simpler.

"0" & Right([Yourtable].[Yourfield], Len([Yourtable].[Yourfield])-2)

can be simplified to:

"0" & Mid([YourTable].[YourField],3)

Mid takes the characters to the end if no third argument is provided.

 

[Kiwiman]

Galaxiom

Kiwiman is onto it but it could be simpler.

"0" & Right([Yourtable].[Yourfield], Len([Yourtable].[Yourfield])-2)

can be simplified to:

"0" & Mid([YourTable].[YourField],3)

Mid takes the characters to the end if no third argument is provided.

Spot on. I'm more used to Excel which requires the 3 arguments for the Mid Funtion (string, start num and num of characters) and assumed that access required the same thing, which is why I used the Right function.

Superb - this is going to make a lot of my updates a lot easier to work out now.

 

[GavZ]

Excellent response thanks very much! Was bugging me all day yesterday!!

 

[Galaxiom]

Spot on. I'm more used to Excel which requires the 3 arguments for the Mid Funtion

Some of the best stuff is discovered here via serendipity. It is why I look through the posts every day and read what might be interesting.

Now I know that Mid is different in Excel too.