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

**Fred Zuckerman** · Nov 12 '05, 10:11 PM

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

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(MyPar ameter,"")). I hope someone with more experience responds...
Fred Zuckerman
San Diego, CA, USA

"Uwe Range" <urange@gmx.d e> wrote in message
news:641c90f5.0 404060624.4b2de d78@posting.goo gle.com...[color=blue]
> 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[/color]

**Terry Kreft** · Nov 12 '05, 10:11 PM

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

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" <urange@gmx.d e> wrote in message
news:641c90f5.0 404060624.4b2de d78@posting.goo gle.com...[color=blue]
> 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[/color]

**NeilAnderson** · Nov 12 '05, 10:11 PM

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

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

urange@gmx.de (Uwe Range) wrote in message news:<641c90f5. 0404060624.4b2d ed78@posting.go ogle.com>...[color=blue]
> 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[/color]

**Terry Kreft** · Nov 12 '05, 10:12 PM

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

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

"NeilAnders on" <neil.anderson@ boroughmuir.edi n.sch.uk> wrote in message
news:83b600a7.0 404061144.4fec5 5c4@posting.goo gle.com...[color=blue]
> 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
>
>
> urange@gmx.de (Uwe Range) wrote in message[/color]
news:<641c90f5. 0404060624.4b2d ed78@posting.go ogle.com>...[color=blue][color=green]
> > 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[/color][/color]

**Uwe Range** · Nov 12 '05, 10:12 PM

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

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_i d) ...').

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

neil.anderson@b oroughmuir.edin .sch.uk (NeilAnderson) wrote in message news:<83b600a7. 0404061144.4fec 55c4@posting.go ogle.com>...[color=blue]
> 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
>
>
> urange@gmx.de (Uwe Range) wrote in message news:<641c90f5. 0404060624.4b2d ed78@posting.go ogle.com>...[color=green]
> > 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[/color][/color]

**rkc** · Nov 12 '05, 10:12 PM

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

"Uwe Range" <urange@gmx.d e> wrote in message
news:641c90f5.0 404060624.4b2de d78@posting.goo gle.com...[color=blue]
> 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?[/color]

Correct way? I dunno.

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

**John Winterbottom** · Nov 12 '05, 10:12 PM

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

"Uwe Range" <urange@gmx.d e> wrote in message
news:641c90f5.0 404070126.25da0 51c@posting.goo gle.com...[color=blue]
> 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_i d) ...').
>
> 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.
>[/color]

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.

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment