Null arguments

[bobfin]

I wrote a new function that has several arguments. The function runs in a query that uses table fields as the arguments. The fields are text, dates and numbers. All I get is "#Error". What happens to the function when a field value is null? Does the function crash and display "#Error"? The code in the function checks for nulls in some arguments, but it never seems to reach the code.

 

[Fizzio]

Unless the argument is designated as optional eg

Function YourFunction(Optional Test as string)

if you pass a null value to the function, it will crash and burn (desparately looking for a more intelligent description of the function failure )

Either designate the argument as optional or avoid passing null values - a workaround is to use the Nz() function in your function call ie

strTest2 = YourOtherFunction(Nz(Me.txtTest,"Default Value"))

 

[bobfin]

Unless the argument is designated as optional eg

Function YourFunction(Optional Test as string)

if you pass a null value to the function, it will crash and burn (desparately looking for a more intelligent description of the function failure )

Either designate the argument as optional or avoid passing null values - a workaround is to use the Nz() function in your function call ie

strTest2 = YourOtherFunction(Nz(Me.txtTest,"Default Value"))

Reply:
The function was made to produce an action-code that depends on 9 table fields. Several of the checks in the code test whether a field is null. So I followed your suggestion and made all the arguments Optional except the first. But when I test in the Immediate Window and skip an argument by entering succesive commas, the test for Not IsNull comes out as true. And it seems a test of IsNull comes out false. What's going on?

 

[Fizzio]

It does also depend on what you are passing to the function, whether you are passing a variable (that can change within the function) or a value. If the argument is left as optional, the function will assume the default for that data type.

To pass a Value to the function, specify that the argument should be ByVal but this is only effective for numbers and dates etc, not for strings or variants. Look at VB help on functions there is some useful info there that may help you.

 

[Rich]

MyFunction(field1,field2,field3 as Variant)

 

[Fizzio]

Rich, does defining all arguments as a variant not open up potential incorrect results from a function. I noticed that you have offered the solution before but in all the literature I've seen, it defines arguments explicitly.
I'm not criticising (before you pull a funny face at me - oh, I forgot, you already are) but I'm interested on how much effect defining the arguments actually has.

 

[bobfin]

It does also depend on what you are passing to the function, whether you are passing a variable (that can change within the function) or a value. If the argument is left as optional, the function will assume the default for that data type.

To pass a Value to the function, specify that the argument should be ByVal but this is only effective for numbers and dates etc, not for strings or variants. Look at VB help on functions there is some useful info there that may help you.

Reply:
I got past a major hump with your help plus KnowledgeBase article 208401. That article lists the default values for each data type when optional arguments are not inialized with a default value. For string variables, test against zero-length string instead of Null. For numbers, test against 0 instead of Null. Now I can get correct results in the Immediate window. But the query where I use the function still returns #Error for all records. It has columns for all the fields the function uses and a column that has the function with the fieldnames in the argument locations. Maybe tomorrow will bring insigt.

 

[Rich]

You don't have to supply the variable's data type in the declaration statement. If you omit the data type, the variable will be of type Variant.


Unless otherwise specified, undeclared variables are assigned the Variant data type. This data type makes it easy to write programs, but it is not always the most efficient data type to use.
You should consider using other data types if:

· Your program is very large and uses many variables.
· Your program must run as quickly as possible.
· You write data directly to random-access files.

 

[Fizzio]

Thanks for that - the grammar suggests it was plagiarised perfectly

 

[Rich]

Research cannot be classed as plagiarism
besides it didn't have the usual copywrite MS

 

[bobfin]

You don't have to supply the variable's data type in the declaration statement. If you omit the data type, the variable will be of type Variant.


Unless otherwise specified, undeclared variables are assigned the Variant data type. This data type makes it easy to write programs, but it is not always the most efficient data type to use.
You should consider using other data types if:

· Your program is very large and uses many variables.
· Your program must run as quickly as possible.
· You write data directly to random-access files.

Reply:
The function is designed to produce a code-value that indicates what action is to be taken with each of our accounts. All the arguments are optional and have their data type declared (no variants). I have a query whose source is a table with 157,000 records. One column has the function name. In the design window, if I type in values for all the arguments, I get correct results, but if I put in fieldnames for the arguments, I get "#Error" in that column. This happens even if I restrict the query to a particular record or just a few records. Why does this happen?

 

[bobfin]

Reply:
The function is designed to produce a code-value that indicates what action is to be taken with each of our accounts. All the arguments are optional and have their data type declared (no variants). I have a query whose source is a table with 157,000 records. One column has the function name. In the design window, if I type in values for all the arguments, I get correct results, but if I put in fieldnames for the arguments, I get "#Error" in that column. This happens even if I restrict the query to a particular record or just a few records. Why does this happen?

Reply #2:
I've found that the "#Error" occurs whenever the table field is null. At least 1 of the 11 fields is null in almost every record.
I thought making the arguments all optional would take care of this situation. Even after specifying default values for every argument (plus the data type), I still get "#Error". I tried another version of the function where all arguments are variants (no defaults) and use IsMissing to check for nulls. That still produces "#Error" on every record. There's got to be a way to handle null arguments in functions that depend on whatever garbage is found in large tables. Any suggestions?

 

[Rich]

Try again,
A user-defined function may return either of these error messages: "Invalid
Use of Null!" or "#Error."

CAUSE
=====

If a field's data type is explicitly defined as any type other than

Variant, Microsoft Access is unable to compare the variable to Null.

RESOLUTION
==========

Define the variable as Variant, which includes the Date/Time, Floating-
point Number, and String data types.

MORE INFORMATION
================

This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the

"Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access versions 1.x and 2.0. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual in Microsoft
Access version 2.0

If the following function is used to evaluate a field that may return a
Null value, the function will return the error "Invalid Use of Null!" or

"#Error" if the field is null:

Function IsFieldNull (MyValue As String)
If IsNull(MyValue) Then
IsFieldNull = "The field is null!"
Else
IsFieldNull = "The field is not null!"
End If
End Function

By using the method in the following function to define the variable
MyValue as a Variant, you will prevent the error "Invalid Use of Null!" or
"#Error," even if the field is null:

Function IsFieldNull (MyValue As Variant)

If IsNull(MyValue) Then
IsFieldNull = "The field is null!"
Else
IsFieldNull = "The field is not null!"
End If
End Function

KBCategory: kbprg kberrmsg
KBSubcategory: PgmOthr
Additional reference words: 1.00 1.10 2.00 7.00 97 8.00 pounderror

Copyright (c) Microsoft Corporation. All rights reserved.

 

[bobfin]

Try again,
A user-defined function may return either of these error messages: "Invalid
Use of Null!" or "#Error."

CAUSE
=====

If a field's data type is explicitly defined as any type other than

Variant, Microsoft Access is unable to compare the variable to Null.

RESOLUTION
==========

Define the variable as Variant, which includes the Date/Time, Floating-
point Number, and String data types.

MORE INFORMATION
================

This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the

"Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access versions 1.x and 2.0. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual in Microsoft
Access version 2.0

If the following function is used to evaluate a field that may return a
Null value, the function will return the error "Invalid Use of Null!" or

"#Error" if the field is null:

Function IsFieldNull (MyValue As String)
If IsNull(MyValue) Then
IsFieldNull = "The field is null!"
Else
IsFieldNull = "The field is not null!"
End If
End Function

By using the method in the following function to define the variable
MyValue as a Variant, you will prevent the error "Invalid Use of Null!" or
"#Error," even if the field is null:

Function IsFieldNull (MyValue As Variant)

If IsNull(MyValue) Then
IsFieldNull = "The field is null!"
Else
IsFieldNull = "The field is not null!"
End If
End Function

KBCategory: kbprg kberrmsg
KBSubcategory: PgmOthr
Additional reference words: 1.00 1.10 2.00 7.00 97 8.00 pounderror

Copyright (c) Microsoft Corporation. All rights reserved.

Reply:
This is the last piece of the puzzle. By changing all the arguments to Variant, I can use IsNull in the function. The function now produces a result for all records. (I still kept Optional on all the arguments). Knowing how Access treats data types in arguments will make writing functions a lot less of a headache. Thanks!

 

[Rich]

Glad it worked, one thing though, please don't keep posting the responses as quotes, many find it annoying