If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
RunSql with " ' " in it...
Hello -
I'm Trying to run an sql statement that I'm creating in the VB code. DoCmd.RunSQL sSQLforUpdate The code is creating an update query ("sSQLforUpdate") that update table 1 from the current form (that bounded to table 2). It is working ok, up until there is a special char such as " ' " in the string that need to be updated (sSQLforUpdate). For example: The word "Hello" will be update ok, but the word "Hell'o" will create an error that wouldn't let me run the query. I've by pass it be using a "remove_Char" function, but I need to find a way to update information that includes also this " ' " sign. Do you know how do I need to write the sql in order to over come this problem? Thank you -- Alu_GK |
#2
|
|||
|
|||
RunSql with " ' " in it...
It is a matter of correctly construction your SQL string.
If you use single quotes as a delimiter and the value you include in the string has a single qoute, you will get the error. For exampe, assume the control txtWorld = Hell'o This will cause the error: strSQL = "UPDATE SomeTable SET MyField = '" & Me.txtWorld & "'" However, if you use two double quotes where the single qoutes are in your assignment, it will correctly delimit the string: strSQL = "UPDATE SomeTable SET MyField = """ & Me.txtWorld & """" "Alu_GK" wrote in message ... Hello - I'm Trying to run an sql statement that I'm creating in the VB code. DoCmd.RunSQL sSQLforUpdate The code is creating an update query ("sSQLforUpdate") that update table 1 from the current form (that bounded to table 2). It is working ok, up until there is a special char such as " ' " in the string that need to be updated (sSQLforUpdate). For example: The word "Hello" will be update ok, but the word "Hell'o" will create an error that wouldn't let me run the query. I've by pass it be using a "remove_Char" function, but I need to find a way to update information that includes also this " ' " sign. Do you know how do I need to write the sql in order to over come this problem? Thank you -- Alu_GK |
#3
|
|||
|
|||
RunSql with " ' " in it...
Of course, if Me.txtWorld contains a double quote (Dave's "Access Shack"),
using double quotes as a delimiter won't work either. I talked about this in my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart Access". You can download the column (and sample database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Klatuu" wrote in message ... It is a matter of correctly construction your SQL string. If you use single quotes as a delimiter and the value you include in the string has a single qoute, you will get the error. For exampe, assume the control txtWorld = Hell'o This will cause the error: strSQL = "UPDATE SomeTable SET MyField = '" & Me.txtWorld & "'" However, if you use two double quotes where the single qoutes are in your assignment, it will correctly delimit the string: strSQL = "UPDATE SomeTable SET MyField = """ & Me.txtWorld & """" "Alu_GK" wrote in message ... Hello - I'm Trying to run an sql statement that I'm creating in the VB code. DoCmd.RunSQL sSQLforUpdate The code is creating an update query ("sSQLforUpdate") that update table 1 from the current form (that bounded to table 2). It is working ok, up until there is a special char such as " ' " in the string that need to be updated (sSQLforUpdate). For example: The word "Hello" will be update ok, but the word "Hell'o" will create an error that wouldn't let me run the query. I've by pass it be using a "remove_Char" function, but I need to find a way to update information that includes also this " ' " sign. Do you know how do I need to write the sql in order to over come this problem? Thank you -- Alu_GK |
#4
|
|||
|
|||
RunSql with " ' " in it...
Hello -
Thanks, both answers where helpful. I think the best way is to chech whether i have a " ' " or " " " in my string and to run the correct sql for the string special chars. Thank you -- Alu_GK "Douglas J. Steele" wrote: Of course, if Me.txtWorld contains a double quote (Dave's "Access Shack"), using double quotes as a delimiter won't work either. I talked about this in my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart Access". You can download the column (and sample database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Klatuu" wrote in message ... It is a matter of correctly construction your SQL string. If you use single quotes as a delimiter and the value you include in the string has a single qoute, you will get the error. For exampe, assume the control txtWorld = Hell'o This will cause the error: strSQL = "UPDATE SomeTable SET MyField = '" & Me.txtWorld & "'" However, if you use two double quotes where the single qoutes are in your assignment, it will correctly delimit the string: strSQL = "UPDATE SomeTable SET MyField = """ & Me.txtWorld & """" "Alu_GK" wrote in message ... Hello - I'm Trying to run an sql statement that I'm creating in the VB code. DoCmd.RunSQL sSQLforUpdate The code is creating an update query ("sSQLforUpdate") that update table 1 from the current form (that bounded to table 2). It is working ok, up until there is a special char such as " ' " in the string that need to be updated (sSQLforUpdate). For example: The word "Hello" will be update ok, but the word "Hell'o" will create an error that wouldn't let me run the query. I've by pass it be using a "remove_Char" function, but I need to find a way to update information that includes also this " ' " sign. Do you know how do I need to write the sql in order to over come this problem? Thank you -- Alu_GK |
#5
|
|||
|
|||
RunSql with " ' " in it...
So what do you consider "the correct sql" when you have both single quotes
and double quotes in your string? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Alu_GK" wrote in message news Hello - Thanks, both answers where helpful. I think the best way is to chech whether i have a " ' " or " " " in my string and to run the correct sql for the string special chars. Thank you -- Alu_GK "Douglas J. Steele" wrote: Of course, if Me.txtWorld contains a double quote (Dave's "Access Shack"), using double quotes as a delimiter won't work either. I talked about this in my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart Access". You can download the column (and sample database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Klatuu" wrote in message ... It is a matter of correctly construction your SQL string. If you use single quotes as a delimiter and the value you include in the string has a single qoute, you will get the error. For exampe, assume the control txtWorld = Hell'o This will cause the error: strSQL = "UPDATE SomeTable SET MyField = '" & Me.txtWorld & "'" However, if you use two double quotes where the single qoutes are in your assignment, it will correctly delimit the string: strSQL = "UPDATE SomeTable SET MyField = """ & Me.txtWorld & """" "Alu_GK" wrote in message ... Hello - I'm Trying to run an sql statement that I'm creating in the VB code. DoCmd.RunSQL sSQLforUpdate The code is creating an update query ("sSQLforUpdate") that update table 1 from the current form (that bounded to table 2). It is working ok, up until there is a special char such as " ' " in the string that need to be updated (sSQLforUpdate). For example: The word "Hello" will be update ok, but the word "Hell'o" will create an error that wouldn't let me run the query. I've by pass it be using a "remove_Char" function, but I need to find a way to update information that includes also this " ' " sign. Do you know how do I need to write the sql in order to over come this problem? Thank you -- Alu_GK |
#6
|
|||
|
|||
RunSql with " ' " in it...
Hello -
Your comment was considered after our discussion here, and eventually I've decided to run the sql considering only situation with " ' ". The way I handle the " " " is that I've sent the string to a "Replace" function to remove the (") if there's any, and remove the option of appearance of " " " in the string, and also the need for 2 sql lines, and especially the bug that would happened if what you suggested will occur. Thanks very much! Have a good weekend. -- Alu_GK "Douglas J. Steele" wrote: So what do you consider "the correct sql" when you have both single quotes and double quotes in your string? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Alu_GK" wrote in message news Hello - Thanks, both answers where helpful. I think the best way is to chech whether i have a " ' " or " " " in my string and to run the correct sql for the string special chars. Thank you -- Alu_GK "Douglas J. Steele" wrote: Of course, if Me.txtWorld contains a double quote (Dave's "Access Shack"), using double quotes as a delimiter won't work either. I talked about this in my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart Access". You can download the column (and sample database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Klatuu" wrote in message ... It is a matter of correctly construction your SQL string. If you use single quotes as a delimiter and the value you include in the string has a single qoute, you will get the error. For exampe, assume the control txtWorld = Hell'o This will cause the error: strSQL = "UPDATE SomeTable SET MyField = '" & Me.txtWorld & "'" However, if you use two double quotes where the single qoutes are in your assignment, it will correctly delimit the string: strSQL = "UPDATE SomeTable SET MyField = """ & Me.txtWorld & """" "Alu_GK" wrote in message ... Hello - I'm Trying to run an sql statement that I'm creating in the VB code. DoCmd.RunSQL sSQLforUpdate The code is creating an update query ("sSQLforUpdate") that update table 1 from the current form (that bounded to table 2). It is working ok, up until there is a special char such as " ' " in the string that need to be updated (sSQLforUpdate). For example: The word "Hello" will be update ok, but the word "Hell'o" will create an error that wouldn't let me run the query. I've by pass it be using a "remove_Char" function, but I need to find a way to update information that includes also this " ' " sign. Do you know how do I need to write the sql in order to over come this problem? Thank you -- Alu_GK |
Thread Tools | |
Display Modes | |
|
|