Replace double quotes

[ssanfu]

I just ran a test..



On a form, I added two text boxes (Text4 & Text6) and a button. The code in the Click event of the button is:

Private Sub Command3_Click()
   
   Me.Text6 = ConvertQuotes(Text4)

End Sub

The code in the function is:

Function ConvertQuotes(InputVal)
   ConvertQuotes = Replace(InputVal, """", "")
End Function

(Expanded: ConvertQuotes = Replace(InputVal, " " " ", "") )



If I type Kay" in Text4 and click the button, Kay is displayed in Text6

If I type "Kay" in Text4, Kay is displayed in Text6.

If I change the button code to:

Private Sub Command3_Click()
   Dim sSQL As String
   
   Me.Text6 = "INSERT INTO Table1(Field1) Values('" & ConvertQuotes(Text4) & "');"
End Sub

and enter "Kay" in Text4, I get:

INSERT INTO Table1(Field1) Values('kay');

It appears to correctly formed..??
Is this what the OP wants?

[June7]

Following Steve's scenario, I created a textbox and input value Kay" then in the Immediate window ran:

?Replace(Forms!form1.Text67,"""",Chr(96))

That works.

Whereas this fails:
?Replace("Kay"","""",Chr(96))

But this works:
x = "Kay" & """
?x
Kay"
?Replace(x,"""",Chr(96))
Kay`

[ssanfu]

It seems to work in code. Since Kay was getting the error on the line

db.Execute strsql, dbFailOnError

I tried changing the button code to this:

Private Sub Command3_Click()
   Dim sSQL As String
   
   sSQL = "INSERT INTO Table1(Field1) Values('" & ConvertQuotes(Text4) & "');"
   MsgBox sSQL
End Sub

I entered "Kay", "Kay"" and ""Kay"" in the text box (not at the same time ).

Every time I got back
INSERT INTO Table1(Field1) Values('kay');

Is this what you are looking for Kay?

[Kay]

it doesn't seem to have problem with single quote

[June7]

Right, Single Quote (apostrophe) not a problem in Replace.

It is the Double Quote (quote mark) that is tricky.

[ssanfu]

OK, lets take a step back.
What are you trying to do? I looks like you are trying to generate an SQL string to insert values into SQL Server.

If so, could you post the code to generate the SQL string?

[Kay]

Yes I was trying to generate SQL string. Here goes my code

Dim strColumn1 As String
Dim stra As String
Dim strb As String
Dim str1A As String
Dim str2B As String
stra = "'"
strb = """"
str1A = "`"
str2B = "`"
strColumn1 = [Forms]![Test]![Column1] & ""
strColumn1 = Replace(strColumn1, stra, str1A)
strColumn1 = Replace(strComment, strb, str2B)

Dim db As Database
Set db = CurrentDb()
strsql = "INSERT INTO Table1(Field1,Field2,Field3,"
strsql = strsql & "Field4,Field5,Field6) "
strsql = strsql & "VALUES (""" & Now() & ""","""
strsql = strsql & [Forms]![Test]![Column1] & ""","""
strsql = strsql & [Forms]![Test]![Column2] & ""","""
strsql = strsql & [Forms]![Test]![Column3] & ""","""
strsql = strsql & [Forms]![Test]![Column4] & ""","""
strsql = strsql & [Forms]![Test]![Column5] & """)"
db.Execute strsql, dbFailOnError
'DoCmd.OpenQuery "QryTest1A", acNormal, acEdit

DoCmd.OpenQuery "QryTest2A", acNormal, acAdd

End If


Thansk

[ssanfu]

strsql = "INSERT INTO Table1(Field1,Field2,Field3,"
strsql = strsql & "Field4,Field5,Field6) "

Field1 is a date field
Fields 2-6 are text fields?

[Kay]

Yep! That is correct.

[June7]

In VBA, criteria for data/time field uses # as delimiter, not apostrophe or paired quotes.

(#" & Now() & "#,"""

You declare and manipulate variable strColumn1 but then don't use it in the SQL construct.

[ssanfu]

This is an example of how I would generate the insert string:

Option Compare Database
Option Explicit
'The above lines should always be at the top of the code page


'Private Sub Something_Click()
   Dim strSQL As String
   Dim db As DAO.Database

   Set db = CurrentDb()

   '   strSQL = "INSERT INTO Table1(Field1,Field2,Field3,Field4,Field5,Field6)"
   strSQL = strSQL & " VALUES (#" & Now() & "#, '"
   strSQL = strSQL & ConvertDbl(ConvertSngl([Forms]![Test]![Column1])) & "', '"
   strSQL = strSQL & ConvertDbl(ConvertSngl([Forms]![Test]![Column2])) & "', '"
   strSQL = strSQL & ConvertDbl(ConvertSngl([Forms]![Test]![Column3])) & "', '"
   strSQL = strSQL & ConvertDbl(ConvertSngl([Forms]![Test]![Column4])) & "', '"
   strSQL = strSQL & ConvertDbl(ConvertSngl([Forms]![Test]![Column5])) & "');"

   'if this is code behind a form, I would use the following
   '      strSQL = "INSERT INTO Table1(Field1,Field2,Field3,Field4,Field5,Field6)"
   '      strSQL = strSQL & " VALUES (#" & Now() & "#, '"
   '      strSQL = strSQL & ConvertDbl(ConvertSngl(Me.[Column1])) & "', '"
   '      strSQL = strSQL & ConvertDbl(ConvertSngl(Me.[Column2])) & "', '"
   '      strSQL = strSQL & ConvertDbl(ConvertSngl(Me.[Column3])) & "', '"
   '      strSQL = strSQL & ConvertDbl(ConvertSngl(Me.[Column4])) & "', '"
   '      strSQL = strSQL & ConvertDbl(ConvertSngl(Me.[Column5])) & "');"

'for debugging purposes, comment out later
   Debug.Print strSQL

   db.Execute strSQL, dbFailOnError

   'DoCmd.OpenQuery "QryTest1A", acNormal, acEdit
   DoCmd.OpenQuery "QryTest2A", acNormal, acAdd

End Sub

'these two subs can be in a standard module or in the same code page as the code above

Function ConvertDbl(InputVal)
'removes double quotes
   ConvertDbl = Replace(InputVal, """", "")
End Function


Function ConvertSngl(InputVal)
'replaced a single apostrophe with double apostrophes
   ConvertSngl = Replace(InputVal, "'", "''")
End Function

[Kay]

thanks a lot! I am getting complie error. will update you later.

[Kay]

Thank you !! It works now.