Updating a field with a comma separated list of values from within VBA

[tonygg]

Hi. Sorry in advance as I am sure this has been answered before but I just cant find it anywhere. It is a question about formatting a query string in VBA to correctly handle comma separated values.

I have a field I need to update in a table in a 3rd party application (I did not design it). The value I need to insert is a string containing comma separated values.



For example:

30s Playtime 2017 Scarlet Dotty Daisy 33214-21, 30s Playtime 2017, 30s Playtime 2017 fabric, Moda 30s Playtime 2017, Chloe's Closet 30s Playtime 2017,

Just to be clear this is a single value of a field. It is meta data to be added to a page on a website.

I create the contents of the field called Me.ProductMeta in VBA and want to run a query to update the value in the product table:

   strUpdate = "UPDATE Product" & vbCrLf & _
    "SET " Product.sSPPMetaKeyWords = " & Chr$(39) & Me.ProductMeta & Chr$(39) & "," & vbCrLf & __
    " WHERE Product.[Product Reference] = " & Chr$(39) & ProductRefAsString & Chr$(39) & ";"


   db.Execute strUpdate, dbFailOnError

Access is complaining as it is seeing the commas in my string Me.ProductMeta as field separators rather than data to be inserted into the field.

Please could someone tell me how I get Access to see them correctly in the update query?

Many thanks

Tony

[ranman256]

I DONT THINK YOU NEED THE vbcrLf,
try
strUpdate = "UPDATE Product SET Product.sSPPMetaKeyWords = '" & Chr$(39) & Me.ProductMeta & Chr$(39) & "', WHERE Product.[Product Reference] = '" & Chr$(39) & ProductRefAsString & Chr$(39) & "'";

[orange]

I agree with ranman you definitely do not need the vbCrLf.
Why i there a comma at the end of you sample string?

Since we don't know what ProductRefAsString is, this is a best guess (untested)

strUpdate = "UPDATE Product SET Product.sSPPMetaKeyWords = '" & Me.ProductMeta & "' WHERE Product.[Product Reference] = '" & ProductRefAsString & "''";

[tonygg]

I agree with ranman you definitely do not need the vbCrLf.
Why i there a comma at the end of you sample string?

Since we don't know what ProductRefAsString is, this is a best guess (untested)

strUpdate = "UPDATE Product SET Product.sSPPMetaKeyWords = '" & Me.ProductMeta & "' WHERE Product.[Product Reference] = '" & ProductRefAsString & "''";

Hi Both. Thank you

Sorry about the extra comma. The real SQL statement updates a bunch of other fields. I removed these for clarity and accidentally left in the comma.

I made the change and it works perfectly. Thank you Didnt think it was this easy. Particularly since I thought I needed to use char(39)s.

Just to check I am usually using char(39) around strings. Can I just use '" & xxxx & "' (single quote then double quote then space then & then space then variable then space then & then space then single quote then double quote)?

Many thanks again

Tony

[June7]

If you used orange's suggested code structure and it works, I expect that answers your question. It's the structure I always use. I have never used Char(39).

BTW, the semi-colon should be within the quote marks (as you originally had) or just eliminated as it is not even necessary for action SQL.

[tonygg]

If you used orange's suggested code structure and it works, I expect that answers your question. It's the structure I always use. I have never used Char(39).

BTW, the semi-colon should be within the quote marks (as you originally had) or just eliminated as it is not even necessary for action SQL.

Yes. Thank you