Dealing with apostrophe's (1 Viewer)

aziz rasul · Aug 3, 2018

I have the following snippet of code which works if strAccumulateHazards does not contain any apostrophe's. However if it does then I get an error in strSQL below. How do I amend the code to prevent such an error?

Code:

            Set rstAccumulateHazards = CurrentDb.OpenRecordset("tblTemp4", dbOpenDynaset)
            
            With rstAccumulateHazards
                Do While Not .EOF
                    If .AbsolutePosition = 0 Then
                        strAccumulateHazards = !hazard
                    Else
                        strAccumulateHazards = strAccumulateHazards & vbCrLf & !hazard
                    End If
                    .MoveNext
                Loop
                .Close
                Set rstAccumulateHazards = Nothing
            End With
            
            strSQL = "UPDATE tblLandParcels SET tblLandParcels.Hazards = '" & strAccumulateHazards & "' WHERE (((tblLandParcels.Hazards) Is Null Or (tblLandParcels.Hazards)='') AND ((tblLandParcels.LongLPID)='" & strLandParcelID & "'));"
            CurrentDb.Execute strSQL

isladogs · Aug 3, 2018

One method is to use the REPLACE function to replace any ‘ with an obscure character then reverse that change afterwards

Sent from my iPhone using Tapatalk

arnelgp · Aug 3, 2018

change it to:

strSQL = "UPDATE tblLandParcels SET tblLandParcels.Hazards = " & Chr(34) & strAccumulateHazards & Chr(34) & " WHERE (((tblLandParcels.Hazards) Is Null Or (tblLandParcels.Hazards)='') AND ((tblLandParcels.LongLPID)=" & Chr(34) & strLandParcelID & Chr(34) & "));"

MajP · Aug 3, 2018

I find this function very helpful in writing sql strings. It handles the apostrophes, trims strings, handles null values and empty strings, and formats dates. It makes writing the sql much easier. Never have to chase delimeters

Code:

Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
            If DateValue(Value) = Value Then
               Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
            Else
               Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
            End If
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Trim(Sql)

End Function

Test

Code:

Public Sub TestSql()
  Dim SomeText As String
  Dim someText2 As String
  Dim someDate As String
  Dim someDateTime As String
  Dim someNumber As String
  Dim someNumber2 As String
  Dim strSql As String
  
  SomeText = CSql("John's Car")
  someText2 = CSql(Null)
  someDate = CSql(Date)
  someDateTime = CSql(Now)
  someNumber = CSql(1)
  someNumber2 = CSql(Null)
  
  strSql = "INSERT INTO someTable (fld1,...fld7) VALUES (" & SomeText & ", " & someText2 & ", " & someDate & ", " _
           & someDateTime & ", " & someNumber & ", " & someNumber2 & ")"
  Debug.Print strSql
End Sub

Results

Code:

INSERT INTO someTable (fld1,...fld7) VALUES ('John''s Car', Null, #08/03/2018#, #08/03/2018 06:15:28#, 1, Null)

aziz rasul · Aug 3, 2018

Thanks all, will investigate asap and get back if I still have issues.

MarkK · Aug 3, 2018

The other way is to use a parameterized QueryDef, which handles delimiters automactically. Consider code like...

Code:

Sub Test19236412()
    Const SQL As String = _
        "UPDATE tblLandParcels As t " & _
        "SET t.Hazards = p0 " & _
        "WHERE (t.Hazards Is Null Or t.Hazards = '') " & _
            "AND t.LongLPID = p1;"

    Dim tmp As String
    
    With CurrentDb.OpenRecordset("tblTemp4", dbOpenDynaset)
        Do While Not .EOF
            tmp = tmp & vbCrLf & !Hazard
            .MoveNext
        Loop
        .Close
    End With
    If Len(tmp) Then tmp = Mid(tmp, 3)[COLOR="Green"] 'drop the leading vbCrLf[/COLOR]
    
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = tmp
        .Parameters(1) = strLandParcelID
        .Execute
        .Close
    End With
End Sub

That is the most readable, most reliable, and fastest way to execute a query you constructed on the fly, and as previously mentioned, all delimiters are handled automatically by the QueryDef.
hth
Mark

Acropolis · Aug 3, 2018

Don't if this is the correct way of doing things or not, but this is how i do it in my system and it seems to work fine:

Code:

INSERT INTO some table (textfield) VALUES ("""& textfieldname &""")

Replace '"& fieldname &"' with """& fieldname &"""

MarkK · Aug 3, 2018

So it would be fine if the value of textfieldname was...

Code:

2 pieces of 8' - 2" x 4"

That wouldn't break your SQL? And in the case of that data, meaning would be lost or changed if you remove or replace either quote type.
Mark

Acropolis · Aug 3, 2018

MarkK said:
So it would be fine if the value of textfieldname was...

Code:

2 pieces of 8' - 2" x 4"

That wouldn't break your SQL? And in the case of that data, meaning would be lost or changed if you remove or replace either quote type.
Mark

Undoubtedly it would, but as I said don't know if its the right way or not, but in the situation in which I use it, it works fine and has done for a couple of years now and solved the problem i was having with apostrophe's.

June7 · Aug 3, 2018

When I build an SQL statement that includes a field with data that can have apostrophe as simple text such as: Carter's Road or 18', I use Replace function to double the apostrophe (this is escaping a special character).

strAccumulateHazards = Replace(!hazard, "'", "''")

As for the quote character in data, couldn't figure out how to do same thing with Replace so instructed users not to use quote character in data. Inches have to be in decimal feet.

MajP · Aug 3, 2018

The function I provided (which incorporates the replace) handles the string

2 pieces of 8' - 2" x 4"
simply becomes
'2 pieces of 8'' - 2" x 4"'
and shows up after insert in the table as
2 pieces of 8' - 2" x 4"

MarkK · Aug 3, 2018

June7 said:
When I build an SQL statement...

Lately, when I build a parameterized SQL statement in code, I always use a DAO.QueryDef.

Mark

NauticalGent · Aug 4, 2018

I cannot offer up a better solution than what has already been given. Some very nifty utilities offered up by the way.

That being said, this topic is very familiar to me since I import and parse text 3-4 times a day. The text I parse are formatted messages that come in via Outlook. I say formatted only in the sense that they are SUPPOSED to be formatted...but just like bowling, everyone has their own way of doing it wrong!

Because the messages came in with Outlook and we were able to link the folder, a simple Append and/or Update query worked and we had no issues moving the entire contents of the message (sometimes up to 10 pages of text) to the appropriate table.

It wasn't until I tried to get cute and use my own SQL statements within a loop that things started getting crazy. All of a sudden I was getting syntax errors that I had NO idea what they meant that I realized I wasn't as cute as I thought I was. I didn't want to go back to my old method so I set about finding out the problem and quickly determined that all it took as an errant quote, apostrophe and who knows what else to throw a monkey wrench into my process.

I set about trying to make a sub much like MajP's but my kung-fu at the time was non-existent so I was cussing like a sailor at my monitor. On the staff there was a electronics technician who just happened to be a CS guru and although his forte was C, Pearl and other languages I never heard of, the basic practice is universal and he started asking questions.

Being the intelligent person he was he quickly realized that somehow when you use a QueryDef, it formats the string and any delimiters with no assistance from you. It was then that he said two things I have not forgotten:

1. Let Access do what Access does best
2. You will NEVER make an idiot-proof application. There is no level of genius that is a match for the idiocy level of your users.

In other words, MarkK's contribution:

That is the most readable, most reliable, and fastest way to execute a query you constructed on the fly, and as previously mentioned, all delimiters are handled automatically by the QueryDef.

...is spot on.

Interestingly enough, it was another thread that MarkK had weighed in on that he mentioned parameterized QueryDefs that made the light bulb burn and gave my my solution to this particular problem.

That's my story and I am sticking to it!

Dealing with apostrophe's (1 Viewer)

aziz rasul

Active member

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

MajP

You've got your good things, and you've got mine.

aziz rasul

Active member

MarkK

bit cruncher

Acropolis

Registered User.

MarkK

bit cruncher

Acropolis

Registered User.

June7

AWF VIP

MajP

You've got your good things, and you've got mine.

MarkK

bit cruncher

NauticalGent

Ignore List Poster Boy

Similar threads

Users who are viewing this thread