访问 VBA：DLookUp 函数给出类型不匹配错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6783142/
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
Access VBA: DLookUp function giving type mismatch error
提问by Shubham
I'm trying to run the following code, but I'm getting a "Type mismatch" compile error on DLookUp.
我正在尝试运行以下代码，但在 DLookUp 上出现“类型不匹配”编译错误。
DLookUp returns a value, right? This code, to me, says: in the strSQL recordset, look for where the SKUS_ORDERED column equals curSKU2. strSQL, temp, curSKU2 are all initialized as string type variables.
DLookUp 返回一个值，对吗？对我来说，这段代码说：在 strSQL 记录集中，查找 SKUS_ORDERED 列等于 curSKU2 的位置。strSQL、temp、curSKU2 都被初始化为字符串类型变量。
...
strSQL = "SELECT * FROM ORDER_DATA WHERE [ORDER]=" & curOrder
Dim temp As String
temp = DLookup("SKUS_ORDERED", db.OpenRecordset(strSQL), SKUS_ORDERED = curSKU2)
...
Where is the type mismatch? Can anyone help me out?
类型不匹配在哪里？谁能帮我吗？
EDIT:
编辑：
...
Set fld_orders = rst_orders.Fields("ORDER")
curOrder = fld_orders.Value
Dim temp As String
temp = DLookup("SKUS_ORDERED", "ORDER_DATA", "SKUS_ORDERED = '" & curSKU2 & "' AND [ORDER] = " & curOrder)
If temp <> Null Then MsgBox temp
...
The entire code is pretty long but here's a larger snippet of where curOrder is initialized, this is inside a nested loop, curSKU2 is initialized earlier outside the loop. Hope it helps.
整个代码很长，但这里有一个更大的片段，说明了 curOrder 在哪里初始化，这是在嵌套循环内，curSKU2 在循环外更早地初始化。希望能帮助到你。
回答by Christian Specht
The mismatch occurs because the second parameter needs to be a string, not a RecordSet.
If any of the parameters in the third argument is a variable (like in your case), the third argument needs to be a concatenated string as well:
发生不匹配是因为第二个参数需要是字符串，而不是 RecordSet。
如果第三个参数中的任何参数是变量（如您的情况），则第三个参数也需要是连接字符串：
temp = DLookup("SKUS_ORDERED", "ORDER_DATA", _
"SKUS_ORDERED = '" & curSKU2 & "' and ORDER = " & curOrder)
EDIT:
编辑：
Without more code, it's difficult to see where you are using Null.
Are the table name and the column names correct?
What types are your variables? Do they really have values?
Can you post some more code where we can see how you declare and fill the variables?
没有更多代码，很难看出您在哪里使用 Null。
表名和列名是否正确？
你的变量是什么类型？他们真的有价值观吗？
你能再贴一些代码让我们看看你是如何声明和填充变量的吗？
The "_" character indicates a line break. I could have written the whole statement in one line, but then you'd have to scroll to see it completely:
“_”字符表示换行。我本可以将整个语句写在一行中，但是您必须滚动才能完整地查看它：
temp = DLookup("SKUS_ORDERED", "ORDER_DATA", "SKUS_ORDERED = '" & curSKU2 & "' and ORDER = " & curOrder)
EDIT 2:
编辑2：
Could you show the parts where both variables are declared and where curSKU2 is initialized as well? With what you posted, one still can't see if curSKU2 is even filled and what types both are.
您能否展示声明两个变量以及初始化 curSKU2 的部分？使用您发布的内容，仍然无法看到 curSKU2 是否已填充以及两者都是什么类型。
Plus, tempis declared as string, so it can never be Null.
This has two consequences:
另外，temp被声明为字符串，所以它永远不能为空。
这有两个后果：
If temp <> Nulldoesn't make sense.- DLookup returns Null when no record is found, so the type mismatch could be in the line
temp = DLookup(...).
Trytemp = Nz(DLookup(...))instead.
If temp <> Null没有意义。- 当没有找到记录时，DLookup 返回 Null，因此类型不匹配可能在行中
temp = DLookup(...)。
试试吧temp = Nz(DLookup(...))。
回答by HansUp
I suggest you change this section of your code ...
我建议您更改代码的这一部分...
Dim temp As String
temp = DLookup("SKUS_ORDERED", "ORDER_DATA", "SKUS_ORDERED = '" & curSKU2 & "' AND [ORDER] = " & curOrder)
to this ...
对这个...
Dim temp As String
Dim strCriteria As String
strCriteria = "SKUS_ORDERED = '" & curSKU2 & "' AND [ORDER] = " & curOrder
Debug.Print strCriteria
Debug.Print TypeName(DLookup("SKUS_ORDERED", "ORDER_DATA", strCriteria))
temp = DLookup("SKUS_ORDERED", "ORDER_DATA", strCriteria)
If you get an error, switch to the Immediate Window to view the output from the Debug.Print statements.
如果出现错误，请切换到立即窗口以查看 Debug.Print 语句的输出。
The first will give you the text for a WHERE condition which you can test in a new query:
第一个将为您提供 WHERE 条件的文本，您可以在新查询中对其进行测试：
SELECT SKUS_ORDERED FROM ORDER_DATA WHERE [strCriteria text here]
The TypeName() function will tell you the data type of the value returned by DLookup(). If TypeName says Null, you will get an error when you try to assign it to a string variable (temp), because a string value can never be Null.
TypeName() 函数将告诉您 DLookup() 返回值的数据类型。如果 TypeName 为 Null，则在尝试将其分配给字符串变量 (temp) 时会出现错误，因为字符串值永远不能为 Null。
