Null value won't pass into function

[mwanstall]

Hi All,

I have started tearing my hair out over this problem!

I am pulling some data from a table and passing it as variables into a function in Access. One of the variables I'm passing through can be Null, so to avoid trying to pass a Null value to a function as a string (which I gather Access wouldn't like) I created an IIF function inside the parameters so if the value was null, it passed the string "NULLDEPOT" into the function, and I dealt with it inside the function.

No matter HOW I try (stacked queries, new variables, taking the IIF out of the function parameters and creating it earlier)...I always get an "#Error" value returned. Even when I set a breakpoint at the top of the function, Access won't trip it if that value was ever Null, however every other value works fine?!

I'll include my code below:

Query code:

VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier Number],IIf([DepotID] Is Null,"NULLDEPOT",[DepotID]))

VB Function Code:

Public Function Supplier(OrigDep As String, AgCE As String, PMC As String, VendNum As String, MainDepot As String) As String
    Dim TempSupplier As String
 
...snip...
 
    ElseIf OrigDep = "13" Then
        If MainDepot = "NULLDEPOT" Then
            TempSupplier = "N"
        ElseIf MainDepot = "13" Then
            TempSupplier = VendNum
        Else
            TempSupplier = "1400000"
        End If
 
...snip...
 
    Else
        TempSupplier = "N"
    End If
 
    ' AgCE and PMC Logic Final Override
    If AgCE = "CE" Or PMC = "V" Then
        TempSupplier = "ZZZ"
    End If
 
    ' Return string
    Supplier = TempSupplier
 
End Function
 

Please HELP!!!

[Rabbit]

Hi All,

I have started tearing my hair out over this problem!

I am pulling some data from a table and passing it as variables into a function in Access. One of the variables I'm passing through can be Null, so to avoid trying to pass a Null value to a function as a string (which I gather Access wouldn't like) I created an IIF function inside the parameters so if the value was null, it passed the string "NULLDEPOT" into the function, and I dealt with it inside the function.

No matter HOW I try (stacked queries, new variables, taking the IIF out of the function parameters and creating it earlier)...I always get an "#Error" value returned. Even when I set a breakpoint at the top of the function, Access won't trip it if that value was ever Null, however every other value works fine?!

I'll include my code below:

Query code:

VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier Number],IIf([DepotID] Is Null,"NULLDEPOT",[DepotID]))

VB Function Code:

Public Function Supplier(OrigDep As String, AgCE As String, PMC As String, VendNum As String, MainDepot As String) As String
    Dim TempSupplier As String
 
...snip...
 
    ElseIf OrigDep = "13" Then
        If MainDepot = "NULLDEPOT" Then
            TempSupplier = "N"
        ElseIf MainDepot = "13" Then
            TempSupplier = VendNum
        Else
            TempSupplier = "1400000"
        End If
 
...snip...
 
    Else
        TempSupplier = "N"
    End If
 
    ' AgCE and PMC Logic Final Override
    If AgCE = "CE" Or PMC = "V" Then
        TempSupplier = "ZZZ"
    End If
 
    ' Return string
    Supplier = TempSupplier
 
End Function
 

Please HELP!!!

Try using Nz() instead of the iif function.

[mwanstall]

Try using Nz() instead of the iif function.

Already tried that Rabbit and I get the same error.

Any other suggestions?

[mwanstall]

Already tried that Rabbit and I get the same error.

Any other suggestions?

Nevermind, found my own answer. All inbound function variables should be declared as Variants and then deal with the NZ() function inside my custom function.

[Rabbit]

Excellent. Always better when the PO finds their own answer.