Replace function inserting Apostrophe (')

[benkingery]

I am having a difficult time with some VBA code running a replace function. Specifically, I'm trying to update a field that contains gender information. The information is contained in my database as follows: "Womens". HOWEVER, I need to run a VBA function to replace all instances of "Womens" with "Women's".

The problem arises with how you must use quotation marks (") and apostrophes(') in VBA to run queries. Any ideas how I can get this to work?

here is what I currently have:

DoCmd.RunSQL "UPDATE Table1 SET Table1 .Field10 = Replace([Table1 ].[Field10 ],'Womens','Women's')"

This obviously doesn't work because of the mismatch of apostrophes and quotation marks.

Thanks in advance.

 

[Mr. B]

Give the following code a try:

Dim strSql As String
strSql = "UPDATE Table1 SET Table1.Field10 = Replace([Table1].[Field10 ],""Womens"",""Women's"")"
CurrentDb.Execute strSql

While it is not necessary to define a variable, it helps to assign your sql statement to a variable so you can use the immediate window to check what the actual value is that is being stored in the variable.

 

[benkingery]

Worked perfectly. Thank you!

I knew there must've just been some syntax issue. Thanks again!