Pass null-value to function (How to..)

[Uwe Range]

Hi to all,

just ran into a problem:
I wrote a function which works with several values collected on a
form. I thought it would be no problem, if the fields were empty,
because I made the function taking care of null-values.

Then I realized that I get an error when I try to pass the values like
that:

DatNext = A_Task_Next_Fct(Me![Task_ID],
Me![Task_Cycle],Me![Date_First, Me![Date_Last])

whenever one of the fields is empty.

What would be the correct way to handle this?

Thanks in advance.

Uwe

[Fred Zuckerman]

Good Question. I will await a response from the experts, too. I think that
functions cannot have null parameters unless they are designated as
optional. I haven't experimented with optional parameters yet. What I do
instead is pass zero-length strings instead of nulls to the function, ie:
MyFunc(NZ(MyParameter,"")). I hope someone with more experience responds...
Fred Zuckerman
San Diego, CA, USA
"Uwe Range" <ur****@gmx.de> wrote in message
news:64**************************@posting.google.c om...

Hi to all,

just ran into a problem:
I wrote a function which works with several values collected on a
form. I thought it would be no problem, if the fields were empty,
because I made the function taking care of null-values.

Then I realized that I get an error when I try to pass the values like
that:

DatNext = A_Task_Next_Fct(Me![Task_ID],
Me![Task_Cycle],Me![Date_First, Me![Date_Last])

whenever one of the fields is empty.

What would be the correct way to handle this?

Thanks in advance.

Uwe

[Terry Kreft]

If the arguments are defined as Variants then you can pass Null values.

e.g.

Function A_Task_Next_Fct( _
varTask_ID as Variant, _
varTask_Cycle as Variant, _
varDate_First as Variant, _
varDate_Last as Variant)
You can also declare it as

Function A_Task_Next_Fct( _
varTask_ID, _
varTask_Cycle, _
varDate_First, _
varDate_Last)

As Variant is the default data type, but by declaring it fully you make it
clear that this is the datatype you want.

--
Terry Kreft
MVP Microsoft Access

"Uwe Range" <ur****@gmx.de> wrote in message
news:64**************************@posting.google.c om...

Hi to all,

just ran into a problem:
I wrote a function which works with several values collected on a
form. I thought it would be no problem, if the fields were empty,
because I made the function taking care of null-values.

Then I realized that I get an error when I try to pass the values like
that:

DatNext = A_Task_Next_Fct(Me![Task_ID],
Me![Task_Cycle],Me![Date_First, Me![Date_Last])

whenever one of the fields is empty.

What would be the correct way to handle this?

Thanks in advance.

Uwe

[NeilAnderson]

Uwe

You say that the function accepts nulls as parameters are you sure?
you haven't written it like A_Task_Next_Fct(myTask as long etc. etc)
If you are sure that it accepts nulls then is this what you are you
passing?
Does me![TaskID] default to 0 for instance?
It would help to know what the function does, what the error message
is & what line of code causes the error.

Neil
ur****@gmx.de (Uwe Range) wrote in message news:<64**************************@posting.google. com>...

Hi to all,

just ran into a problem:
I wrote a function which works with several values collected on a
form. I thought it would be no problem, if the fields were empty,
because I made the function taking care of null-values.

Then I realized that I get an error when I try to pass the values like
that:

DatNext = A_Task_Next_Fct(Me![Task_ID],
Me![Task_Cycle],Me![Date_First, Me![Date_Last])

whenever one of the fields is empty.

What would be the correct way to handle this?

Thanks in advance.

Uwe

[Terry Kreft]

Neil,
He doesn't say the function accepts nulls, he says the function takes care
of nulls. The first is a subset of the second.

--
Terry Kreft
MVP Microsoft Access

"NeilAnderson" <ne***********@boroughmuir.edin.sch.uk> wrote in message
news:83**************************@posting.google.c om...

Uwe

You say that the function accepts nulls as parameters are you sure?
you haven't written it like A_Task_Next_Fct(myTask as long etc. etc)
If you are sure that it accepts nulls then is this what you are you
passing?
Does me![TaskID] default to 0 for instance?
It would help to know what the function does, what the error message
is & what line of code causes the error.

Neil
ur****@gmx.de (Uwe Range) wrote in message

news:<64**************************@posting.google. com>...

Hi to all,

just ran into a problem:
I wrote a function which works with several values collected on a
form. I thought it would be no problem, if the fields were empty,
because I made the function taking care of null-values.

Then I realized that I get an error when I try to pass the values like
that:

DatNext = A_Task_Next_Fct(Me![Task_ID],
Me![Task_Cycle],Me![Date_First, Me![Date_Last])

whenever one of the fields is empty.

What would be the correct way to handle this?

Thanks in advance.

Uwe

[Uwe Range]

First of all thanks for the response to all.

I think I didn't explain it properly: The function does not accept
null values when they are passed to it. I can only handle null values
within the function (by checking with 'if not IsNull(myTask_id) ...').

Maybe Terry's suggestion to declare the variables as variant will
solve the problem. Otherwise I will have to test each value for null
values before passing it to the function and replace it with 0,
#1/1/1900#, "", ... which I would like to avoid.

Thanks again.

Uwe
ne***********@boroughmuir.edin.sch.uk (NeilAnderson) wrote in message news:<83**************************@posting.google. com>...

Uwe

You say that the function accepts nulls as parameters are you sure?
you haven't written it like A_Task_Next_Fct(myTask as long etc. etc)
If you are sure that it accepts nulls then is this what you are you
passing?
Does me![TaskID] default to 0 for instance?
It would help to know what the function does, what the error message
is & what line of code causes the error.

Neil
ur****@gmx.de (Uwe Range) wrote in message news:<64**************************@posting.google. com>...

Hi to all,

just ran into a problem:
I wrote a function which works with several values collected on a
form. I thought it would be no problem, if the fields were empty,
because I made the function taking care of null-values.

Then I realized that I get an error when I try to pass the values like
that:

DatNext = A_Task_Next_Fct(Me![Task_ID],
Me![Task_Cycle],Me![Date_First, Me![Date_Last])

whenever one of the fields is empty.

What would be the correct way to handle this?

Thanks in advance.

Uwe

[rkc]

"Uwe Range" <ur****@gmx.de> wrote in message
news:64**************************@posting.google.c om...

Hi to all,

just ran into a problem:
I wrote a function which works with several values collected on a
form. I thought it would be no problem, if the fields were empty,
because I made the function taking care of null-values.

Then I realized that I get an error when I try to pass the values like
that:

DatNext = A_Task_Next_Fct(Me![Task_ID],
Me![Task_Cycle],Me![Date_First, Me![Date_Last])

whenever one of the fields is empty.

What would be the correct way to handle this?

Correct way? I dunno.

A way, make the arguments optional and don't pass them if
they are null.

[John Winterbottom]

"Uwe Range" <ur****@gmx.de> wrote in message
news:64**************************@posting.google.c om...

First of all thanks for the response to all.

I think I didn't explain it properly: The function does not accept
null values when they are passed to it. I can only handle null values
within the function (by checking with 'if not IsNull(myTask_id) ...').

Maybe Terry's suggestion to declare the variables as variant will
solve the problem. Otherwise I will have to test each value for null
values before passing it to the function and replace it with 0,
#1/1/1900#, "", ... which I would like to avoid.

A common way to handle this is to coalesce the null to a known value having
the correct data type, e.g. zero for numeric types, which you can then test
for in the function. Use the Nz function.