访问VBA，转义单引号，替换（），和空 [英] Access VBA, unescaped single quotes, Replace(), and null

问题描述

工作在微软VBA脚本采取了大规模的平数据库和大约20种不同的表之间的分裂它。该脚本主要由在平板数据库打开表，检查每一行，以确保它不是一个重复，再加入必要的字段。重复为每个表。

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.

我第一次跑这一切都进行得很顺利，直到我试图处理名称奥马利。我认为这是很明显出了什么问题。在谷歌快速搜索止跌回升此相关的StackOverflow的帖子。我把他们的意见，并添加替换（STR，'，''）来各个领域输入其插入新的表之前。现在，我已经遇到一个新的问题，谷歌是没有多大帮助。

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.

替换（NULL，'，''）导致运行时错误，与扁平数据库只是充斥着空值。我可以添加上面的一个额外的行每 替换（）打电话以检查 ISNULL（）如果是的话把空到数据库中，而不是替换（STR，'，''），虽然我会preFER可以放入如果可能的话一行的解决方案。有没有更好的方法来解决这一难题，否则我将在我的code需要216 如果语句？

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 would 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?

编辑 -

这是我在寻找一个更优雅的解决方案的另一个原因是我的重复检查，code。目前，我有类似如下：

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

由于替换（）电话是内嵌的重复检查，如果我要改用如果语句检查空那我就不得不要么结果保存到一个字符串或更新到平板数据库。该函数返回替换（STR，'，''）或空，而不需要额外的变量将是理想的。

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.

推荐答案

如果你想保留一切在线，您可以使用立即如果函数（ IIF ）：

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

使用它作为这样：

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

这篇关于访问VBA，转义单引号，替换（），和空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！