VBA空日期引數

我正在嘗試在更新查詢中使用 VBA UDF 訪問，但它不起作用。這是我的功能：

Function final_forecast(status As String, _
    Optional LDP As String, _
    Optional fgpo_mode As String, _
    Optional fgpo_start As Date, _
    Optional fgpo_piw As Date, _
    Optional asn_mode As String, _
    Optional asn_piw As Date, _
    Optional maker As Double, _
    Optional origin As Double, _
    Optional water As Double, _
    Optional pod As Double, _
    Optional transit As Double, _
    Optional manual_piw As Date = 0, _
    Optional delivery As Date = 0) As Date
    
    Dim this_week, in_transit_piw, at_factory_piw, semi_final_piw, final_piw As Date
    
    this_week = Date - Weekday(Date, 1)   7
    
    If status = "In Transit" Then
        If Not delivery = 0 Then
            in_transit_piw = delivery
        ElseIf Not manual_piw = 0 Then
            in_transit_piw = manual_piw
        ElseIf asn_mode = "A" Then
            in_transit_piw = asn_piw
        Else:
            in_transit_piw = DateAdd("d", nz(water, 0), asn_piw)
        End If
    ElseIf status = "At Factory" Then
        If fgpo_mode = "A" Then
            at_factory_piw = fgpo_piw
        ElseIf LDP = "LDP" Then
            at_factory_piw = DateAdd("d", nz(maker, 0)   nz(origin, 0)   nz(water, 0), fgpo_start)
        Else:
            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 fgpo_mode = "A" Then
        semi_final_piw = fgpo_piw
    ElseIf at_factory_piw < DateAdd("d", 21   nz(water, 0), Date) Then
        If 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
    
    If semi_final_piw < this_week Then
        final_piw = this_week
    Else:
        final_piw = semi_final_piw
    End If
    
    final_forecast = final_piw
    
    
End Function

這是一個拋出“編譯錯誤：預期運算式”的示例行。

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 個引數是我表中的空日期欄位。我一直在尋找答案。任何人都可以幫忙嗎？

編輯：

更新查詢如下所示：

UPDATE new_in_dc 
SET 
new_in_dc.[Final PIW] = final_forecast([new_in_dc].[In Transit Status],
[new_in_dc].[LDP],
[new_in_dc].[FGPO Mode Ship],
[new_in_dc].[FGPO Start Ship Dt],
[new_in_dc].[FGPO Prj In Wh Dt],
[new_in_dc].[Asn Mode Shp],
[new_in_dc].[Asn Prj In Wh Dt],
[new_in_dc].[FGPO Maker],
[new_in_dc].[Origin Port Delay],
[new_in_dc].[Water Delay],
[new_in_dc].[POD Delay],
[new_in_dc].[Transit Delay],
[new_in_dc].[Manual PIW],
[new_in_dc].[Delivery Date]);

uj5u.com熱心網友回復：

您可以（應該）洗掉引數串列中的最后兩個逗號以 final_forecast

當按位置傳遞引數時，你只需要引數串列中間的逗號：如果最后它們應該被留下。

如果您嘗試在資料庫中運行這樣的查詢會發生什么？

select final_forecast(t.[In Transit Status],
t.[LDP],
t.[FGPO Mode Ship],
t.[FGPO Start Ship Dt],
t.[FGPO Prj In Wh Dt],
t.[Asn Mode Shp],
t.[Asn Prj In Wh Dt],
t.[FGPO Maker],
t.[Origin Port Delay],
t.[Water Delay],
t.[POD Delay],
t.[Transit Delay],
t.[Manual PIW],
t.[Delivery Date])
from new_in_dc t

uj5u.com熱心網友回復：

洗掉到兩個尾隨逗號，它將運行：

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

如果您在查詢中使用該函式，因為您可能對某些必填欄位的某些記錄具有Null值，請使用Nz提供有效值，例如對于最后兩個引數，今天的日期：

a:final_forecast("In Transit",Nz([FieldA],"N/A"),"B",#6/23/21#,#9/20/21#,"B",#8/11/21#,0,0,27,0,0,Nz([FieldB],Date()),Nz([FieldC],Date()))

uj5u.com熱心網友回復：

我必須將所有內容都包裝在 Nz([Field],0) 中才能獲取更新查詢以使我的 UDF 作業。

UPDATE new_in_dc SET new_in_dc.[Final PIW] = final_forecast([new_in_dc].[In Transit Status],nz([new_in_dc].[LDP],0),nz([new_in_dc].[FGPO Mode Ship],0),nz([new_in_dc].[FGPO Start Ship Dt],0),nz([new_in_dc].[FGPO Prj In Wh Dt],0),nz([new_in_dc].[Asn Mode Shp],0),nz([new_in_dc].[Asn Prj In Wh Dt],0),nz([new_in_dc].[FGPO Maker],0),nz([new_in_dc].[Origin Port Delay],0),nz([new_in_dc].[Water Delay],0),nz([new_in_dc].[POD Delay],0),nz([new_in_dc].[Transit Delay],0),nz([new_in_dc].[Manual PIW],0),nz([new_in_dc].[Delivery Date],0));

標籤：vba ms-access

上一篇：SelectCase-Loop無紋系列

下一篇：VBA-從不同的字串創建動態變數