问题描述
工作在微软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,转义单引号,替换(),和空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!