“条件表达式中的数据类型不匹配。”ms access出错，我完成了所有建议的故障排除

Question

我得到错误“条件表达式中的数据类型不匹配”在查询中。

像这样查询工作完美

WorkingDays([ORDER_NOTIFICATION_DATE],[OP_DISTRIBUTION_DATE]) AS
BOOKING_DAYS, IIf([BOOKING_DAYS]>8,"IS LATE","ON TIME") AS BOOKING_DELAYED
FROM JOB INNER JOIN [ORDER] ON JOB.[JOB_ID] = ORDER.[JOB_ID]
WHERE (((ORDER.ORDER_NOTIFICATION_DATE) Is Not Null) AND
((ORDER.OP_DISTRIBUTION_DATE) Is Not Null));

当我尝试输入另一个标准时，它会显示错误：

WorkingDays([ORDER.ORDER_NOTIFICATION_DATE],[ORDER.OP_DISTRIBUTION_DATE]) AS 
BOOKING_DAYS, IIf([BOOKING_DAYS]>8,"IS LATE","ON TIME") AS BOOKING_DELAYED
FROM JOB INNER JOIN [ORDER] ON JOB.[JOB_ID] = ORDER.[JOB_ID]
WHERE (((ORDER.ORDER_NOTIFICATION_DATE) Is Not Null) AND 
((ORDER.OP_DISTRIBUTION_DATE) Is Not Null) AND
((WorkingDays([ORDER.ORDER_NOTIFICATION_DATE],
[ORDER.OP_DISTRIBUTION_DATE]))>8));

WorkingDays返回一个整数，我尝试了其他帖子中提出的大多数解决方案。

这是工作日：

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name:     WorkingDays
' Inputs:   StartDate As Date
'   EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date:     February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer



intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1  
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

Answer 1

我看到您使用的是Is Not Null和一个不能在同一个查询中处理空值的函数。

虽然您可能认为这可以很好地工作，但当空值被过滤掉时，它们仍然会被传递到此函数并创建错误。

使用Nz对传递给函数的空值进行转义：

WorkingDays(Nz([ORDER.ORDER_NOTIFICATION_DATE], 0),Nz([ORDER.OP_DISTRIBUTION_DATE], 0)) AS 
BOOKING_DAYS, IIf([BOOKING_DAYS]>8,"IS LATE","ON TIME") AS BOOKING_DELAYED
FROM JOB INNER JOIN [ORDER] ON JOB.[JOB_ID] = ORDER.[JOB_ID]
WHERE (((ORDER.ORDER_NOTIFICATION_DATE) Is Not Null) AND 
((ORDER.OP_DISTRIBUTION_DATE) Is Not Null) AND
((WorkingDays(Nz([ORDER.ORDER_NOTIFICATION_DATE], 0),
Nz([ORDER.OP_DISTRIBUTION_DATE], 0)))>8));