Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Quote and double quote

    In SQL string I'm building an Insert statement, based on the data in a recordset.

    However, it reates a huge amount of problems, as some of the records in the recordsets CAN contain single quotes, and thus breaks the sentence apart.

    Any of the values surrounded with ' (single quote) is a string (varchar) field from a recordset in a sql-server db.

    So how do I avoid the single quotes? I considered building a small function that parses the string and removes singe quote's, but if there's an easier way, I'd like to know.

    Cheers, Flix

    Code:
        SQL = "INSERT INTO md_tbl_rapportering values(" & txt_foreid.Value & _
            "," & txt_nummer.Caption & "," & subform_udvalg![Musanummer].Value & _
            "," & subform_udvalg![Gramex-ID].Value & _
            ",'" & subform_udvalg!Plademærkenavn.Value & "'," & subform_udvalg!Plademærkenummer.Value & _
            "," & subform_udvalg!selskabsnummer.Value & ",'" & subform_udvalg!selskabsnavn.Value & _
            "','" & subform_udvalg!katalognummer.Value & "','" & subform_udvalg!Indspilningslandekode.Value & _
            "'," & subform_udvalg!Indspilningsår.Value & "," & subform_udvalg!side.Value & _
            "," & subform_udvalg!Tracknummer.Value & ",'" & subform_udvalg![Album Titel].Value & _
            "','" & subform_udvalg![track Titel].Value & "','" & subform_udvalg![Hoved artist].Value & "'," & _
            mintid & "," & minsek & ",0,0,0,'')"
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    This is my personal approach when dealing with single or double quotes: I stay away from issuing Executes. I go with recordsets and poke the "offending" strings accross that way ... Either DAO or ADO it doesn't matter.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    I wound up writing this little function. Solves the quotation issue.

    Code:
    Public Function FjernCit(tekststreng As String) As String
    
        Dim lengde
        Dim plads
        Dim returstreng
        
        lengde = Len(tekststreng)
        
        For i = 1 To lengde
        
            If Mid(tekststreng, i, 1) <> "'" Then returstreng = returstreng & Mid(tekststreng, i, 1)
        
        Next i
    
        FjernCit = returstreng
    
    End Function
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you're going to do it that way, you might as well use Replace()...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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