[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
>