VBA Null Date Argument-CodePudding

I am trying to use a VBA UDF in access in an update query, but its not working. Here is my function:

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

Here is a sample row that is throwing a "Compile Error: Expected Expression".

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

The last 2 arguments are empty date fields in my table. I have searched high and low for an answer. Can anyone help?

EDIT:

The update query looks like this:

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]);

CodePudding user response：

You can (should) remove those last two commas in the list of arguments to final_forecast

When passing arguments by position, you only need the commas in the middle of the arguments list: if at the end they should be left off.

What happens if you try to run a query like this in your database?

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

CodePudding user response：

Remove to two trailing commas and it will run:

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

If you use the function in a query whery you may have Null values for some records for a required field, use Nz to provide a valid value, for example for the last two arguments, the date of today:

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()))