Access VBA, unescaped single quotes, Replace(), and null

Working on a script in Microsoft VBA to take a massive flat database and split it between about 20 different tables. The script consists mainly of opening a table, checking every row in the flat database to make sure it's not a duplicate, then adding the necessary fields. Repeat for every table.

The first time I ran it everything was going well until I tried to process the name O'Malley. I think it's obvious what went wrong. A quick search on Google turned up this related StackOverflow post. I took their advice and added Replace(str, "'", "''") to every field before inputting it into the new tables. Now I've run into a new problem and Google is less helpful.

Replace(null, "'", "''") causes a run-time error, and the flat database is just riddled with null values. I can add an extra line above every Replace() call to check IsNull() and if so put null into the database instead of Replace(str, "'", "''"), although I wou开发者_如何学JAVAld prefer a solution that can fit into a single line if possible. Is there any more elegant way to solve this dilemma, or will I need 216 If statements in my code?

EDIT -

Another reason that I'm searching for a more elegant solution is my duplicate checking code. At the moment I have something like the following:

        'Check for duplicates
        'Assume student is duplicate if it shares:
        '   (StudentName and School) or SSN
        Set rstDuplicate = CurrentDb.OpenRecordset("select * from Student where (StudentName = '" & Replace(rstFrom("Student").Value, "'", "''") & "' AND School = '" & Replace(rstFrom("School").Value, "'", "''") & "') OR SSN = '" & Replace(rstFrom("Social").Value, "'", "''") & "'")
        If rstDuplicate.RecordCount = 0 Then
            'Duplicate was not found
            rstTo.AddNew
            ' Add fields to the new table
            rstTo.Update
        End If

Since the Replace() calls are inline with the duplicate checking, if I were to instead use If statements to check for null then I would have to either save the result to a string or update to flat database. A function that returns Replace(str, "'", "''") OR null without the need for extra variables would be ideal.

If you want to keep everything inline, you can use an immediate If function (IIf):

IIf(IsNull(rstFrom("Student").Value), " Is Null", "= " & Replace(rstFrom("Student").Value)

That will be a nightmare to read and maintain, though. You are better off writing your own function to handle the change in comparison operator as well as the apostrophe escaping:

Function CompFld(Val As Variant) As String
    If IsNull(Val) Then
        CompFld = " Is Null "
    Else
        CompFld = "= '" & Replace(Val, "'", "''") & "' "
    End If
End Function

Use it as so:

Dim SQL As String
SQL = "SELECT * FROM Student " & _
      "WHERE (StudentName " & CompFld(rstFrom("Student").Value) & " AND " & _
      "       School " & CompFld(rstFrom("School").Value) & ") " & _
      "   OR (SSN " & CompFld(rstFrom("Social").Value) & ") "
Set rstDuplicate = CurrentDb.OpenRecordset(SQL)
If rstDuplicate.RecordCount = 0 Then
    'Duplicate was not found
    rstTo.AddNew
    ' Add fields to the new table
    rstTo.Update
End If

A terse, yet ugly little gem handed down to me from ages ago:

Replace(str & "", "'", "''")

Appending an empty string to a null value returns an empty string in VBA, and won't modify a non-empty string.

Access' database engine will accept either single or double quotes as delimiters for string values in queries. So instead of ...

SELECT * FROM Student WHERE StudentName = 'O''Malley'

... you can do this ...

SELECT * FROM Student WHERE StudentName = "O'Malley"

That would allow you to handle inputs which contain apostrophes. OTOH, if your string inputs also contain double quotes, this will break.

I suspect you may have more going on than just the apostrophe issue, but I don't understand your big picture. You seem to be opening a third DAO recordset for each record in rstFrom, to check whether a match exists in the Student table. I would use DCount() instead.

Dim strCriteria As String
strCriteria = "StudentName = ""O'Malley"" AND School = ""foo"""
If DCount("*", "Student", strCriteria) = 0 Then
    'no match found --> add it '
End If