Access更新查询传空日期参数调用VBA UDF报预期表达式错误求助

1. 测试调用语法错误 #

你测试代码里的函数调用末尾多了多余的逗号，VBA不允许可选参数列表最后出现无意义的逗号，原报错代码：

a = final_forecast("In Transit",,"B",#6/23/21#,#9/20/21#,"B",#8/11/21#,0,0,27,0,0,,)

把末尾多余的逗号删掉即可解决编译报错，修改后：

a = final_forecast("In Transit",,"B",#6/23/21#,#9/20/21#,"B",#8/11/21#,0,0,27,0,0)
Debug.Print a

2. 函数参数类型不匹配表空值 #

Access表中的空日期字段不是0，而是Null，但你当前函数里的日期参数都声明为Date类型，该类型无法接收Null值，查询传入空字段时会触发类型不匹配错误。需要做如下修改：

• 将所有可能传入空值的可选参数类型改为Variant
• 用IsNull判断参数是否为空，不要直接和0比较
• 原默认值=0对日期空值没有实际意义，可以删除，改用空值判断逻辑

3. 变量声明不规范 #

你当前的变量声明语句只有最后一个变量被定义为Date类型，前面的变量默认是Variant类型，容易引发隐式类型转换错误：

' 错误写法：仅final_piw是Date类型，其余为Variant
Dim this_week, in_transit_piw, at_factory_piw, semi_final_piw, final_piw As Date
' 正确写法：每个变量单独声明类型
Dim this_week As Date, in_transit_piw As Date, at_factory_piw As Date, semi_final_piw As Date, final_piw As Date

Function final_forecast(status As String, _
    Optional LDP As Variant, _
    Optional fgpo_mode As Variant, _
    Optional fgpo_start As Variant, _
    Optional fgpo_piw As Variant, _
    Optional asn_mode As Variant, _
    Optional asn_piw As Variant, _
    Optional maker As Variant, _
    Optional origin As Variant, _
    Optional water As Variant, _
    Optional pod As Variant, _
    Optional transit As Variant, _
    Optional manual_piw As Variant, _
    Optional delivery As Variant) As Date
    
    Dim this_week As Date, in_transit_piw As Date, at_factory_piw As Date, semi_final_piw As Date, final_piw As Date
    ' 初始化日期为当前周，避免空值比较错误
    this_week = Date - Weekday(Date, 1) + 7
    in_transit_piw = this_week
    at_factory_piw = this_week
    
    If status = "In Transit" Then
        If Not IsNull(delivery) And delivery <> 0 Then
            in_transit_piw = delivery
        ElseIf Not IsNull(manual_piw) And manual_piw <> 0 Then
            in_transit_piw = manual_piw
        ElseIf Not IsNull(asn_mode) And asn_mode = "A" Then
            in_transit_piw = asn_piw
        ElseIf Not IsNull(asn_piw) Then
            in_transit_piw = DateAdd("d", Nz(water, 0), asn_piw)
        End If
    ElseIf status = "At Factory" Then
        If Not IsNull(fgpo_mode) And fgpo_mode = "A" Then
            at_factory_piw = fgpo_piw
        ElseIf Not IsNull(LDP) And LDP = "LDP" Then
            at_factory_piw = DateAdd("d", Nz(maker, 0) + Nz(origin, 0) + Nz(water, 0), fgpo_start)
        ElseIf Not IsNull(fgpo_piw) Then
            at_factory_piw = DateAdd("d", Nz(maker, 0) + Nz(origin, 0) + Nz(water, 0), fgpo_piw)
        End If
    End If
    
    If in_transit_piw < this_week Then
        semi_final_piw = this_week
    ElseIf Not IsNull(fgpo_mode) And fgpo_mode = "A" Then
        semi_final_piw = fgpo_piw
    ElseIf at_factory_piw < DateAdd("d", 21 + Nz(water, 0), Date) Then
        If Not IsNull(LDP) And LDP = "LDP" Then
            semi_final_piw = this_week
        Else
            semi_final_piw = DateAdd("d", 28 + Nz(maker, 0) + Nz(origin, 0) + Nz(water, 0), Date)
        End If
    Else
        semi_final_piw = at_factory_piw
    End If
    
    final_piw = IIf(semi_final_piw < this_week, this_week, semi_final_piw)
    final_forecast = final_piw
    
End Function

原更新查询语句不需要修改，修改函数后直接运行即可，表中的空日期字段会被函数自动识别为Null处理，不会再触发类型错误。

内容的提问来源于stack exchange，提问作者Mateyobi