Update query (VBA SQL) to replace part of the field

[BHo15]

I am stumped. I am trying to use a SQL statement in VBA to replace part of a field in a table. It works great when I use the SQL statement when building an Update Query, but I keep getting errors when I use it in VBA (mostly Runtime 3075).

Here is the SQL statement I want to use...

strSQL = "UPDATE tblSymbols SET tblSymbols.StockchartsLink = '" & Left("StockchartsLink", InStr(1, "StockchartsLink", "=p") + 1) & strP & Mid("StockchartsLink", InStr(1, "StockchartsLink", "=p") + 12, 50) & "' WHERE ((StockchartsLink <>''))"

The idea is to replace "p" number in a URL with a new number (strP). Here is a sample URL...

http://stockcharts.com/h-sc/ui?s=A&p=D&b=5&g=0&id=p29873932357&a=44529210&listNum=127

I want to replace the bolded text.

I can use RegEx if I loop through each record of the table, but it would be much faster and easier using an Update statement.

Thoughts?

[jforbes]

These highlighted parts will be literal values in the resultant SQL:

strSQL = "UPDATE tblSymbols SET tblSymbols.StockchartsLink = '" & Left("StockchartsLink", InStr(1, "StockchartsLink", "=p") + 1) & strP & Mid("StockchartsLink", InStr(1, "StockchartsLink", "=p") + 12, 50) & "' WHERE ((StockchartsLink <>''))"

it either needs to be a VBA variable or the code needs to be tweaked so that the LEFT(), MID() and InStr() functions are evaluated by SQL. If you want a SQL version of it, use CHARINDEX() instead of InStr().

Also, your probably going to have an error when the Mid() function is evaluated. It probably won't like 50. If there aren't enough characters after the "=p" you'll get a runtime error. You'll need to calculate the last parameter for the Mid() by taking the Length of "StockchartsLink" and subtracting the length to "=p", plus the additional replaceable characters.

[BHo15]

Thanks for the feedback. I was not familiar with CHARINDEX, but that appears to be isolated to SQL Server and the like, and not so with VBA SQL (unless I am missing a library).

I thought about the use of variables, but since this is a mass "Find and Replace", I can't know what would go in each of the Left, Mid, and Instr's until each row is evaluated.

If I am missing something, please let me know.

[jforbes]

I'm so very used to using TSQL (SQL SERVER) syntax when writing a SQL Statement. If you don't have SQL SERVER, you can forget all about the CHARINDEX().

It sounds like you want to build up bulk update SQL statement in a String and send it to Access to process. In this case you'll want to use something like this:

1.strSQL = "UPDATE tblSymbols SET tblSymbols.StockchartsLink = Left([StockchartsLink], InStr(1, [StockchartsLink], '=p') + 1) & '" & strP & "' & Mid([StockchartsLink], InStr(1, [StockchartsLink], '=p') + 12, 50) WHERE ((StockchartsLink <>''))"

This way SQL uses the value of the actual field [StockchartsLink] in the Left(), InStr() and Mid() functions and uses the value stored in the VBA variable strP.

Mixing and matching VBA and SQL can be tricky. One thing that can help with this is to put a breakpoint in your code right after the SQL has been determined, and then you can print it into the Immediate Window using "?strP". Then you can copy and paste the SQL into a new query (in SQL View) and try to run it to see what errors Access experiences. Also you can tweak it to get it running and then reproduce those tweaks in your VBA Code. ...You may already know all this.

[BHo15]

Well... It worked. What you used is very similar to what I started with at first, but you did something different, and something that I have never seen before.

There are ampersands inside of the double quotes for the SQL statement?

+ 1) & '" & strP & "' & Mid

What is the logic behind that?

[jforbes]

The Ampersand asks SQL to concatenate the separate string values together. You could also use a Plus(+) sign when inside the quotes.