Escape characters - SQL string

[Snowflake68]

Not sure if the title of my post is correct but I am trying to build an SQL query string in VBA which creates a temporary table containing 3 fields. However the field called 'Description' contains Apostrophes which then break the query and produce an error.

The error is a run-time error '3075' Syntax error (missing operator) in query expression "O'Donnel'.

How do I amend the SQL string below so that it takes into account that the Description field may contain apostrophes? Ive played around with this for a while now and just cannot get the syntax correct.

Here is my code:

Dim strSQL2 As String
        While [Forms]![frmPreviewSelectionSubForm].frmPreviewSelectionDatasheet.Form.CurrentRecord < [Forms]![frmPreviewSelectionSubForm].frmPreviewSelectionDatasheet.Form.Recordset.RecordCount
            strSQL2 = "INSERT INTO TempSortOrder (BIN, ListReference, Description) VALUES ('" & Forms!frmPreviewSelectionSubForm.frmPreviewSelectionDatasheet.Form!BIN & "', '" & Forms!frmPreviewSelectionSubForm.frmPreviewSelectionDatasheet.Form!ListReference & "', '" & Forms!frmPreviewSelectionSubForm.frmPreviewSelectionDatasheet.Form!Description_2 & "');"
                        
            DoCmd.RunSQL strSQL2
    DoCmd.GoToRecord Record:=acNext
        Wend

 

[plog]

1. Why are you making a temporary table with 3 fields? What do you do later with that data? Usually temporary tables are hacks around not knowing able to query or apply filters.

2. Use the Replace function (https://www.techonthenet.com/access/functions/string/replace.php). The escape character for a single quote is two single quotes. Replace all instances of 1 quote with 2:

Replace([YourString], "'", "'")

 

[isladogs]

Replace all instances of 1 quote with 2:

Replace([YourString], "'", "'")

I think plog meant

Replace([YourString], "[COLOR="red"]'[/COLOR]", "[COLOR="Red"]''[/COLOR]")

I've made it bigger & in RED so its easier to see

 

[Snowflake68]

1. Why are you making a temporary table with 3 fields? What do you do later with that data? Usually temporary tables are hacks around not knowing able to query or apply filters.

2. Use the Replace function (https://www.techonthenet.com/access/functions/string/replace.php). The escape character for a single quote is two single quotes. Replace all instances of 1 quote with 2:

Replace([YourString], "'", "'")

Thanks but I cannot replace the characters I need to keep them in the text. I am creating a temporary table because I am resorting the data which is complex and intertwined with lots of other functionality which I do not want to have to revisit. I just need to know how to amend my syntax so that the apostrophes in the text does not break the query.

 

[isladogs]

You should still replace single ' with double ' ' (but read my corrected version) then run the query
If the outcome is a problem, reverse it afterwards

Alternatively, see this link for further details & a slightly different approach http://allenbrowne.com/casu-17.html

 

[plog]

Thanks but I cannot replace the characters I need to keep them in the text

I don't think you understood my post. You asked for the escape character, I gave it. Reread my advice, give it a test and see if it works.

 

[Snowflake68]

I think plog meant

Replace([YourString], "[COLOR="red"]'[/COLOR]", "[COLOR="Red"]''[/COLOR]")

I've made it bigger & in RED so its easier to see

Thanks but I dont want to replace any of the text I need to keep it as it is. Is there another way that you can suggest please? I just need to get the syntax correct so that it doesn't see the text in the field as part of the string. Think I need to put the apostrophes somewhere but just cant seem to get it right.

 

[Snowflake68]

I don't think you understood my post. You asked for the escape character, I gave it. Reread my advice, give it a test and see if it works.

yeah you are correct I didnt and still dont really understand your post. Ive reread your advice and tried by changing my code to that of the below but still get an error.

strSQL2 = Replace("INSERT INTO TempSortOrder (BIN, ListReference, Description) VALUES ('" & Forms!frmPreviewSelectionSubForm.frmPreviewSelectionDatasheet.Form!BIN & "', '" & Forms!frmPreviewSelectionSubForm.frmPreviewSelectionDatasheet.Form!ListReference & "', '" & Forms!frmPreviewSelectionSubForm.frmPreviewSelectionDatasheet.Form!Description_2 & "');", "'", "''")

:banghead:

 

[Minty]

They are not replacing the apostrophe - they are adding sufficient characters around it so that it gets inserted correctly.

Another way would be to parameterise your query. Have a look here https://access-programmers.co.uk/forums/showpost.php?p=1535632&postcount=7 for an example

 

[Snowflake68]

They are not replacing the apostrophe - they are adding sufficient characters around it so that it gets inserted correctly.

Another way would be to parameterise your query. Have a look here https://access-programmers.co.uk/forums/showpost.php?p=1535632&postcount=7 for an example

Thanks Minty but this code you have pointed me at is way beyond my understanding and current capability.

I have tried the replace function as suggested and as per my previous post but it still errors.

 

[Snowflake68]

Just by pure luck and not really by any real skill I have achieved what I need it to do. This is my code. I just needed to know where to put the escape characters but somewhere along the line the replies just confused me.

Thanks to everyone for your time and patience with a damsel in distress but I got there in the end

 strSQL2 = "INSERT INTO TempSortOrder (BIN, ListReference, Description) VALUES ('" & Forms!frmPreviewSelectionSubForm.frmPreviewSelectionDatasheet.Form!BIN & "', '" & Forms!frmPreviewSelectionSubForm.frmPreviewSelectionDatasheet.Form!ListReference & "', '' & Forms!frmPreviewSelectionSubForm.frmPreviewSelectionDatasheet.Form!Description_2 & '');"

 

[Gasman]

I believe you need to make the replace on the actual data control and not the whole sql string?

So create as many variables as needed for the substitution(s)

strDescription = Replace(Forms!frmPreviewSelectionSubForm.frmPreviewSelectionDatasheet.Form!Description_2,"'","''")

then use strDescription in place of the form control in your sql string

You could incorporate the replace in the string concatenation, but to me that would be unweildy. I like to assemble sql strings in pieces, that way it is easier to spot your errors, and I make quite a few.

Thanks Minty but this code you have pointed me at is way beyond my understanding and current capability.

I have tried the replace function as suggested as per my previous post but it still errors.

 

[Snowflake68]

I believe you need to make the replace on the actual data control and not the whole sql string?

So create as many variables as needed for the substitution(s)

strDescription = Replace(Forms!frmPreviewSelectionSubForm.frmPreviewSelectionDatasheet.Form!Description_2,"'","''")

then use strDescription in place of the form control in your sql string

You could incorporate the replace in the string concatenation, but to me that would be unweildy. I like to assemble sql strings in pieces, that way it is easier to spot your errors, and I make quite a few.

Thanks for this. I will try it as it does make sense to create variables. Think I was just trying to sort it out too quickly as Im up against a deadline with only the help from you guys on here. I always do try and solve things myself first but there comes a time when you just want someone to show you the way.

 

[Gasman]

If you are anything like me, you search for ages, try stuff, and it does not work. So you post here, and then go off and search again whilst waiting for a reply, and then find the answer.