返回变体的 vba 函数的“无效使用 null”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7891921/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"invalid use of null" for vba function that returns variant
提问by sigil
I'm trying to write a VBA function that gets a value from an Access table using a SELECT query. Here's the code:
我正在尝试编写一个 VBA 函数，该函数使用 SELECT 查询从 Access 表中获取值。这是代码：
Function getTableValue(uniqueID As Long, tableName As String, _
idField As String, tableField As String) As Variant
Dim db As Database
Dim rs As Recordset
Dim selectSQL As String
selectSQL = "select * from " & tableName & " where " & idField & "=" & uniqueID
Set db = OpenDatabase(dbPath)
Set rs = db.OpenRecordset(selectSQL)
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
getTableValue = rs.Fields(tableField)
End If
End Function
When the field specified by tableFieldis a Date/Time type and the field is empty, getTableValue()returns an "Invalid use of Null" error. I thought that having the function return a Variant would allow me to return Null values; what should I do to handle these empty dates?
当由 指定的字段tableField为日期/时间类型且该字段为空时，getTableValue()返回“无效使用 Null”错误。我认为让函数返回一个 Variant 将允许我返回 Null 值；我应该怎么做来处理这些空日期？
回答by sigil
It turns out that the function's Null value was being passed to a Date/Time variable. I've fixed that by using the following:
结果是函数的 Null 值被传递给一个日期/时间变量。我已经使用以下方法解决了这个问题：
dim myDate as date
dim myVar as variant
myVar=getTableValue(idNum,"thisTable","idField", "valueField")
if isNull(myVar) then
myDate=0
else
myDate=myVar
endif
While it doesn't check whether or not myVarreturns a Date, valueField's values are of type Date/Time so it's ok.
虽然它不检查是否myVar返回 Date，但它valueField的值属于 Date/Time 类型，所以没关系。
回答by TJ Amas
回答by Friedrich
Maybe using Nz ? getTAbleValue = Nz(rsFields(tableField), SomeReplacementDate)
也许使用 Nz ？getTAbleValue = Nz(rsFields(tableField), SomeReplacementDate)
I guess the point is while using the return value from this function. Either you alway return somethin proper or you have to check the return value of this function.
我想重点是在使用这个函数的返回值时。要么你总是返回正确的东西，要么你必须检查这个函数的返回值。
I have to admit I do not know what a null Date may mean. And well do you have an idea what that could be?
我不得不承认我不知道空日期可能意味着什么。你知道那可能是什么吗？
回答by Joseph Delgado
I would have changed the function to return a string. Then use nz()to handle the null by making the string zero-length.
我会更改函数以返回一个字符串。然后nz()通过使字符串长度为零来处理空值。
getTableValue = nz(rs.Fields(tableField),"")
