Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2002
    Posts
    23

    Exclamation Unanswered: INSERTING text with Apostrophes

    Does anyone know how to avoid the Run-Time 3075 error when trying to INSERT data into a table which contains apostrophes? I have form (Form1) that has a text box (Text1) and am using the INSERT statement through SQL to send the data to a table. I'm using a command button (command0) to insert the value of the text box into a table (table1). This is the code I'm using:

    Private Sub Command0_Click()
    Dim strSQL As String
    Dim qdf As QueryDef

    strSQL = "INSERT INTO Table1 (field1) VALUES('" & Text1 & "')"
    Set qdf = CurrentDb.CreateQueryDef("", strSQL)
    qdf.Execute
    End Sub

    Any suggestions would be wonderful as to how I can go about inserting values both with and without apostrophes successfully. Thanks in advance for the help!

    LMF
    Last edited by littlemadfox; 01-16-04 at 16:01.

  2. #2
    Join Date
    May 2003
    Posts
    144
    Try this. You need to add another apostrophe to the existing apostrophe in the name.


    Private Sub Command0_Click()
    Dim strSQL As String
    Dim qdf As QueryDef

    strSQL = "INSERT INTO Table1 (usrname) VALUES('" & Replace(Text1, "'", "''") & "')"
    Set qdf = CurrentDb.CreateQueryDef("", strSQL)
    qdf.Execute

    End Sub


    SHK

  3. #3
    Join Date
    Jul 2002
    Posts
    23
    I am using Access 97 and it doesn't have the Replace function. Any other thoughts?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by littlemadfox
    I am using Access 97 and it doesn't have the Replace function. Any other thoughts?
    Use a recordset then ...

    SELECT * FROM MyTable WHERE (1=0);

    Are you using ADO or DAO?

  5. #5
    Join Date
    Jul 2002
    Posts
    23
    DAO

    How could I go about using a RecordSet to take care of apostrophe issues? Could you give a little more instruction about that?

    PLEASE POST ANY THOUGHTS!

    Thanks for the input!
    Last edited by littlemadfox; 01-16-04 at 16:02.

  6. #6
    Join Date
    May 2003
    Posts
    144
    Can you use INSTR function in 97?

  7. #7
    Join Date
    Jul 2002
    Posts
    23
    Originally posted by SHK
    Can you use INSTR function in 97?
    Yes, I can use that function. The only concern I have is if the string entered has 2 apostrophe's in it. Such as:

    Bob's Car Doesn't Run

    Typically, if I were writing a query for this, I could just do
    INSERT INTO table (field) VALUES( "Bob's Car Doesn't Run" )

    and that would work. As long as it's surrounded in double quotes it works, but I'm not sure how to do it in VBA so that anywhere there are quotes, they are taken care of.

    Would using InStr work for this? If so, what would code look like to accomplish this?

  8. #8
    Join Date
    May 2003
    Posts
    144
    Try this.

    Dim strPartText As String
    Dim strFinalText As String
    Dim ipos As Integer
    Dim strSQL As String
    Dim qdf As QueryDef

    strPartText = Text1

    Do While InStr(strPartText, "'") > 0
    ipos = InStr(strPartText, "'")
    strFinalText = strFinalText + Left(strPartText, ipos) + "'"
    strPartText = Right(strPartText, Len(strPartText) - ipos)
    Loop
    strFinalText = strFinalText + strPartText

    strSQL = "INSERT INTO Table1 (field1) VALUES('" & strFinalText & "')"
    Set qdf = CurrentDb.CreateQueryDef("", strSQL)
    qdf.Execute


    SHK

  9. #9
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Did your try the CHR(34) ?

  10. #10
    Join Date
    Dec 2003
    Posts
    13
    Originally posted by hammbakka
    Did your try the CHR(34) ?

    hi

    apostrophes is reserved for parameter passing syntex it will give error
    write a function which will replace apostrophes with ` (press key near to numeric 1) , it will work

    regards
    nanaiah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •