[AccessD] Outputing nulls in query with function

You use a function to perform the calculation and return a NULL where you
want to.  All you have to do is either not declare the return type or
specifically declare it a variant.  If you don't declare the return type it
is automatically a variant.  A variant can be set to NULL.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob Heygood
Sent: Sunday, November 18, 2007 12:18 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Outputing nulls in query with function

Hello Arthur

As I said in my first message and in the title this is all happening in a
Query, not a table. 
Sorry if that was not clear.
This column does not appear in the underlying table.
It is output by my function. Per your example: my function would compute the
number of children. If it could not do so, I want a null returned.

I want the query to output a value computed by my function or contain a
null. Maybe this is not possible??


TIA

Bob




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Sunday, November 18, 2007 9:04 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Outputing nulls in query with function

First step would be to ensure that all values in the column are nulls when
they are not meaningful (although as Susan pointed out, null is meaningful).
What I mean is that zero is not equivalent to null.

You can do this at the table-design level by changing the properties to
"allow null = yes" and then erase the default value property, which tends to
go to zero, and that's not what you want. So once you have eliminated the
zeroes and replaced them will nulls, then you're in business, and the front
end won't let you deposit zeroes.

Even that oversimplifies, and sorry about that. Suppose, for example, the
column was called NumberOfChildren. The answer, as in my case, is zero. That
is a valid answer, and different from null, which indicates the absence of
information, not its presence. Retrofitting this analysis to existing data
may be non-trivial. If you don't know how many kids I have, then the
appropriate value is Null. If you do know that I have no kids, then the
appropriate value is zero. Either way, you have to resolve the existing data
before you can enforce it at the table-level. I hope that you don't have a
lot of data already recorded with this ambiguity. If you do, then you don't
have much choice but to check all the questionable values (does zero mean no
kids or we don't know?). That's not going to be easy, but sometimes accuracy
outweighs easiness.

hth,
Arthur

On 11/18/07, Bob Heygood <bheygood at abestsystems.com> wrote:
>
> Yes, we need to do things like count and such and use TAStats.
>
> Thanks for replying.
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan 
> Harkins
> Sent: Sunday, November 18, 2007 8:34 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Outputing nulls in query with function
>
> > I have a query that outputs a column that uses a user function to 
> > create it.
> > Works great.
> > The client wants the results in that column to either be a value or 
> > null, not an empty string or zero. We are going to do some 
> > statistical jive later down the road.
> > So, how can I output a null?
>
> =======Null has a specific meaning -- forcing it might usurp that, are 
> you sure you really want to do that?
>
> Susan H.
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com