Replace Function used in a query

[mcrowson]

I'm using the replace function to select a "," and put in an "&". I only want to replace once and I need it to to start after x characters, we will say 5 just for this example.

Replace([string],",","&",5,1)

That is the command I am using.

The problem is that if I use that function, it deletes all characters before 5

This is what I start with

• Alfaro, L., Charlton
• Baum, J. R., Locke, E. A.

This is what I end with

• ro & L., Charlton
• & J. R., Locke, E. A.

Any idea why the start attribute for replacing is removing all characters before it? I would like to keep all characters, but just begin searching and replacing after 5.

 

[Brianwarnock]

From Help

The return value of the Replace function is a string, with substitutions made, that begins at the position specified by start and and concludes at the end of the expression string. It is not a copy of the original string from start to finish.

Brian

 

[Brianwarnock]

Try

Left([fld],InStr(5,[fld],",")-1) & " &" & Mid([fld],InStr(5,[fld],",")+1)

replacing Fld and 5 with the appropriate values.

Brian

 

[mcrowson]

concatenating left with replace works, thanks!

 

[Brianwarnock]

How does that work? The Left will pull in upto the character before the "," , but the replace will pull in from the Start postion which may be before the "," , thus giving duplication.
I would be interested to see the actual code.

Brian

 

[!Blue]

Or even

Left([string],5) & Replace([string],",","&",5,1)

 

[mcrowson]

It works perfectly for me. Because the replacement function cut off everything to the right of point X, A left before then works perfectly

Left([column1],X-1) & Replace([column1],"m","n",X)

Replace starts at position X, and cuts off everything before it, as per my original question, so having all of the characters to the left of that point just fills in what I'm missing.

 

[Brianwarnock]

Yes of course, I was still thinking of the Left upto the "," with the instr function

Brian