Null value won't pass into function

M

m.wanstall

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:

Example data being passed in is as follows:

Supplier("13","AG","S","JL884","NULLDEPOT")

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!!!
 
S

Smartin

m.wanstall said:
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?!
Click to expand...

Do you know about Nz?

Try using Nz([possible null field], value if null)

Query code:
=========================
VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
Number],Nz([DepotID],"NULLDEPOT"))

Hoping this helps,
 
M

m.wanstall

m.wanstall said:
Hi All,
Click to expand...
I have started tearing my hair out over this problem!
Click to expand...
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.
Click to expand...
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?!
Click to expand...

Do you know about Nz?

Try using Nz([possible null field], value if null)

Query code:
=========================
VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
Number],Nz([DepotID],"NULLDEPOT"))

Hoping this helps,
Click to expand...

Thanks Smartin, I'd already tried using NZ(). It also returns
"#Error"?!

I am truly stumped. If it won't even worked with a stacked query (ie
using the IIF statement in one query, then using another query to
import the result of that IIF statement and passing it through the
function -- that STILL returns an Error for those records that were
originally Null) I just don't understand it?! How would Access have
ANY memory that those records were originally Null? My IIF statement
should have turned the Null option into a String (which it does when I
ran the query...but the error occurs on the stacked query when I try
to pass it into the function still).
 
S

Smartin

m.wanstall said:
m.wanstall said:
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?!
Click to expand...
Do you know about Nz?

Try using Nz([possible null field], value if null)

Query code:
=========================
VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
Number],Nz([DepotID],"NULLDEPOT"))

Hoping this helps,
Click to expand...

Thanks Smartin, I'd already tried using NZ(). It also returns
"#Error"?!

I am truly stumped. If it won't even worked with a stacked query (ie
using the IIF statement in one query, then using another query to
import the result of that IIF statement and passing it through the
function -- that STILL returns an Error for those records that were
originally Null) I just don't understand it?! How would Access have
ANY memory that those records were originally Null? My IIF statement
should have turned the Null option into a String (which it does when I
ran the query...but the error occurs on the stacked query when I try
to pass it into the function still).
Click to expand...

Hmm, well I'm not totally following this, but another option is to
change your function so the parameters that could be null are declared
as Variant. Maybe:

Public Function Supplier(OrigDep As String, AgCE As String, PMC As
String, VendNum As String, MainDepot As Variant) As String
Dim TempSupplier As String

Then add code immediately after the declarations to handle the possible
null:

MainDepot = Nz(MainDepot, "NULLDEPOT")

HTH,
 
M

m.wanstall

m.wanstall said:
m.wanstall wrote:
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?!
Do you know about Nz?
Try using Nz([possible null field], value if null)
Query code:
=========================
VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
Number],Nz([DepotID],"NULLDEPOT"))
Hoping this helps,
Click to expand...
Click to expand...
Thanks Smartin, I'd already tried using NZ(). It also returns
"#Error"?!
Click to expand...
I am truly stumped. If it won't even worked with a stacked query (ie
using the IIF statement in one query, then using another query to
import the result of that IIF statement and passing it through the
function -- that STILL returns an Error for those records that were
originally Null) I just don't understand it?! How would Access have
ANY memory that those records were originally Null? My IIF statement
should have turned the Null option into a String (which it does when I
ran the query...but the error occurs on the stacked query when I try
to pass it into the function still).
Click to expand...

Hmm, well I'm not totally following this, but another option is to
change your function so the parameters that could be null are declared
as Variant. Maybe:

Public Function Supplier(OrigDep As String, AgCE As String, PMC As
String, VendNum As String, MainDepot As Variant) As String
Dim TempSupplier As String

Then add code immediately after the declarations to handle the possible
null:

MainDepot = Nz(MainDepot, "NULLDEPOT")

HTH,
Click to expand...

Thanks Smartin,

Tried that and it's still throwing an "#Error".

I should point out that the values in the DepotID are NULL because of
a join that I've done in the query where I only include values from
TableB when they;re in TableA. TableB holds the DepotID and not all
PartNumbers (in TableA) have a matching DepotID so that's how I get my
Null value (not sure if this makes any kind of difference).

In case it helps anyone I'll include the full functions code without
the SNIPs and the actual SQL code that invokes the function and the
data as it is passed through so hopefully someone can recreate my
error if need be:

[SQL CODE]
....
Supplier("13",[AGCECOM],[PMC],[Supplier Number],IIf([DepotID] Is
Null,"NULLDEPOT",[DepotID])) AS VendorCode
....
[/SQL CODE]


[DATA]
....
VendorCode: Supplier("13","AG","S","JL8898",Null)
--OR if the IIF works it should send--
VendorCode: Supplier("13","AG","S","JL8898","NULLDEPOT")
....
[/DATA]


[VB CODE]
Public Function Supplier(OrigDep As String, AgCE As String, PMC As
String, VendNum As String, MainDepot As Variant) As String
Dim TempSupplier As String

MainDepot = Nz(MainDepot, "NULLDEPOT")

' Depot 1
If OrigDep = "10" Then
TempSupplier = "1300000"

' Depot 2
ElseIf OrigDep = "13" Then
If MainDepot = "NULLDEPOT" Then
TempSupplier = "N"
ElseIf MainDepot = "13" Then
TempSupplier = VendNum
Else
TempSupplier = "1400000"
End If

' Depot 3
ElseIf OrigDep = "14" Then
If MainDepot = "NULLDEPOT" Then
TempSupplier = ""
ElseIf MainDepot = "14" Then
TempSupplier = VendNum
Else
TempSupplier = "1300000"
End If

' Depot not 13, 14 or 10...error
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
[/VB CODE]
 
R

Robert Morley

VendorCode: Supplier("13","AG","S","JL8898",Null)
--OR if the IIF works it should send--
VendorCode: Supplier("13","AG","S","JL8898","NULLDEPOT")
Click to expand...

If you're getting this from a table, don't forget to use
MyTable!MyField.Value (with the .Value being the important part here) if
you're passing this to a Variant. If you just use MyTable!MyField, you end
up passing the whole field, which may or may not end up working, but is
probably not what you intended to pass.
[VB CODE]
Public Function Supplier(OrigDep As String, AgCE As String, PMC As
String, VendNum As String, MainDepot As Variant) As String
Click to expand...

Try chaging all of these to, for example, "ByVal OrigDep As String, ByVal
AgCE As String", etc...unless any of them actually need to be ByRef, but
that wasn't my understanding of your code.
Dim TempSupplier As String

MainDepot = Nz(MainDepot, "NULLDEPOT")
Click to expand...

Try putting a Debug.Print MainDepot here, to be absolutely certain that it's
getting the correct value. If you *are* passing the entire field by
accident, then this is actually trying to change the field value, which is
probably being ignored because the joined field is not updatable in this
instance.

Other than that, your code looks good to me, but definitely check on how
you're passing the value, because I suspect that might be the source of the
problem here.



Rob
 
M

m.wanstall

Hi,
you have to declare all arguments here as variant:
Public Function Supplier(OrigDep As String, AgCE As String, PMC As
String, VendNum As String, MainDepot As String) As String

and then test for null inside this function

--
Best regards,
___________
Alex Dybenko (MVP)http://alexdyb.blogspot.comhttp://www.PointLtd.com




Hi All,
Click to expand...
I have started tearing my hair out over this problem!
Click to expand...
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.
Click to expand...
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?!
Click to expand...
I'll include my code below:
Click to expand...
Example data being passed in is as follows:
Supplier("13","AG","S","JL884","NULLDEPOT")
Click to expand...

Query code:
=========================
VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
Number],IIf([DepotID] Is Null,"NULLDEPOT",[DepotID]))
=========================
Click to expand...
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

ElseIf OrigDep = "13" Then
If MainDepot = "NULLDEPOT" Then
TempSupplier = "N"
ElseIf MainDepot = "13" Then
TempSupplier = VendNum
Else
TempSupplier = "1400000"
End If

Else
TempSupplier = "N"
End If
Click to expand...
' AgCE and PMC Logic Final Override
If AgCE = "CE" Or PMC = "V" Then
TempSupplier = "ZZZ"
End If
Click to expand...
' Return string
Supplier = TempSupplier
Click to expand...
End Function
=========================
Click to expand...
Please HELP!!!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks for that Alex, that did the trick!

Can I ask WHY that is the case if I'm only ever passing Strings into
the other variables?
 
A

Alex Dybenko

Hi,
Can I ask WHY that is the case if I'm only ever passing Strings into
the other variables?
Click to expand...

even all fields has value - null can be passed to function on new record, or
while form is loaded, and once you get an error in such function - if
returns for all records. This is what I guess, don't know for sure.

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
J

John Spencer

Are you sure that all the fields in all the rows have a value?
IF [AGCECOM], [PMC], or [Supplier Number} is null in any record then you
would get an error when you have declared the arguments as string in the
function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

There is nothing preventing you from defining variables as Variant in a
declaration statement. Why not just declare that parameter (MainDepot) as a
variant. Then, in you function, test to see if it is null

IF ISNULL(MainDepot) Then
TempSupplier = "N"
ELSEIF MainDepot = "13" then
...

I frequently define my parameters as variants and test for NULL values in my
functions, especially those that involve passing the values from fields that
have some possibility of being NULL.

HTH
Dale
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

InStr Function Help 3
Function problems 9
null value pass from form into query 2
Function returning null value instead of string value 3
Checking Date for Null 14
Cannot pass Null value for parameter ??? 3
Access 2003 treats embedded functions in nested queries as a reference rather than a value - Access 2
DCount improper use of null 1

Top