Combating apostrophes

[Ian Mac]

All,

I have the following code to insert records into temporary tables on the client.

DoCmd.RunSQL "DELETE * FROM tblTEMPNTCChild;"

strSQL = ""
strSQL = "SELECT tblNTCChild.NTCChildID, "
strSQL = strSQL & "[1stForename], "
strSQL = strSQL & "[2ndForename], "
strSQL = strSQL & "Surname, "
strSQL = strSQL & "Dob, "
strSQL = strSQL & "Dod, "
strSQL = strSQL & "Urn, "
strSQL = strSQL & "OutputCode "
strSQL = strSQL & "FROM tblNTCChild "
strSQL = strSQL & "INNER JOIN tblQualifingNTCChildren "
strSQL = strSQL & "ON tblNTCChild.Entid = tblQualifingNTCChildren.EntID "
strSQL = strSQL & "WHERE tblNTCChild.Entid = " & sglEntID & ";"

Set qryDef = db.CreateQueryDef("")

With qryDef

    .SQL = strSQL
    Set rst = .OpenRecordset()
    
    If rst.RecordCount = 0 Then Exit Sub
    
    Do While Not rst.EOF
    
        'Add the record + the leading number based on i
    
    strSQL = ""
    strSQL = strSQL & "INSERT INTO tblTEMPNTCChild ( "
    strSQL = strSQL & "TEMP_NTC_Number, "
    strSQL = strSQL & "NtcChildId, "
    strSQL = strSQL & "[1stForename], "
    strSQL = strSQL & "[2ndForename], "
    strSQL = strSQL & "Surname, "
    strSQL = strSQL & "Dob , "
    strSQL = strSQL & "Dod, "
    strSQL = strSQL & "Urn, "
    strSQL = strSQL & "OutputCode ) "
    strSQL = strSQL & "VALUES ("
    strSQL = strSQL & i & ","
    strSQL = strSQL & rst!NtcChildId & ", '"
    strSQL = strSQL & rst![1stForename] & "', '"
    strSQL = strSQL & rst![2ndForename] & "', '"
    strSQL = strSQL & rst!Surname & "', "
    strSQL = strSQL & rst!Dob & ", "
    strSQL = strSQL & Nz(rst!Dod, 0) & ", '"
    strSQL = strSQL & rst!Urn & "', "
    strSQL = strSQL & Nz(rst!OutputCode, 0) & ");"
    
    DoCmd.RunSQL strSQL
    
    rst.MoveNext
    i = i + 1
    
    Loop


End With

The above is just an extract.

The breaks down when any of the [1stForename], [2ndForename] or Surname
fields contain an apostrophy, e.g. O'Neil

Also, you will see I have used Nz() for fields which could be null values,

Is there a way round this without putting in a zero.

Thanks,

 

[izyrider]

i can offer you a workaround:

in a module
public function aposWrite(strWithApos) as string
aposWrite = replace(strWithApos, "'", "´")
end function

and then:
strSQL = strSQL & aposWrite(rst!Surname) & "', "


if the difference between O´Brian and O'Brian is offensive to your aesthetics
you might also want:
public function aposRead(strWithApos) as string
aposRead = replace(strWithApos, "´", "'")
end function


izy

 

[Ian Mac]

Excellent, I'm using Access 97 so I've had to use the Replace97 Function.
I already had a similar function but didn't think to use ´ I also can't find that character on the keyboard, so the finished artical ends up as:

Public Function Remove(s As String)

Remove = Replace97(s, "'", Chr(180))

End Function

Cheers,

 

[izyrider]

great that the workaround works

if this is a thing that happens often, you might consider a dedicated function combining stripped-down aposWrite() & Replace97() functionality to avoid the call-of-call overhead. it's certainly not worth it for a single call to fill your one-off strSQL, but in a bulk-record operation the overhead could be significant.

also - an error in my original:
public function aposWrite(strWithApos) as string
should have been (the non-variant, hence faster)
public function aposWrite(strWithApos as string) as string

izy

 

[Ian Mac]

It may happen often.

But we are moving to Access 2003 in a month, that's when I can down right funky with the developement with no A97 limitations.

Cheers,