By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,745 Members | 1,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,745 IT Pros & Developers. It's quick & easy.

Null value won't pass into function

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier Number],IIf([DepotID] Is Null,"NULLDEPOT",[DepotID]))
VB Function Code:
Expand|Select|Wrap|Line Numbers
  1. Public Function Supplier(OrigDep As String, AgCE As String, PMC As String, VendNum As String, MainDepot As String) As String
  2.     Dim TempSupplier As String
  3.  
  4. ...snip...
  5.  
  6.     ElseIf OrigDep = "13" Then
  7.         If MainDepot = "NULLDEPOT" Then
  8.             TempSupplier = "N"
  9.         ElseIf MainDepot = "13" Then
  10.             TempSupplier = VendNum
  11.         Else
  12.             TempSupplier = "1400000"
  13.         End If
  14.  
  15. ...snip...
  16.  
  17.     Else
  18.         TempSupplier = "N"
  19.     End If
  20.  
  21.     ' AgCE and PMC Logic Final Override
  22.     If AgCE = "CE" Or PMC = "V" Then
  23.         TempSupplier = "ZZZ"
  24.     End If
  25.  
  26.     ' Return string
  27.     Supplier = TempSupplier
  28.  
  29. End Function
  30.  
Please HELP!!!
May 18 '07 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,364
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:
Expand|Select|Wrap|Line Numbers
  1. VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier Number],IIf([DepotID] Is Null,"NULLDEPOT",[DepotID]))
VB Function Code:
Expand|Select|Wrap|Line Numbers
  1. Public Function Supplier(OrigDep As String, AgCE As String, PMC As String, VendNum As String, MainDepot As String) As String
  2.     Dim TempSupplier As String
  3.  
  4. ...snip...
  5.  
  6.     ElseIf OrigDep = "13" Then
  7.         If MainDepot = "NULLDEPOT" Then
  8.             TempSupplier = "N"
  9.         ElseIf MainDepot = "13" Then
  10.             TempSupplier = VendNum
  11.         Else
  12.             TempSupplier = "1400000"
  13.         End If
  14.  
  15. ...snip...
  16.  
  17.     Else
  18.         TempSupplier = "N"
  19.     End If
  20.  
  21.     ' AgCE and PMC Logic Final Override
  22.     If AgCE = "CE" Or PMC = "V" Then
  23.         TempSupplier = "ZZZ"
  24.     End If
  25.  
  26.     ' Return string
  27.     Supplier = TempSupplier
  28.  
  29. End Function
  30.  
Please HELP!!!
Try using Nz() instead of the iif function.
May 18 '07 #2

P: 3
Try using Nz() instead of the iif function.
Already tried that Rabbit and I get the same error.

Any other suggestions?
May 18 '07 #3

P: 3
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.
May 18 '07 #4

Rabbit
Expert Mod 10K+
P: 12,364
Excellent. Always better when the PO finds their own answer.
May 18 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.