Linking on field that allows nulls

[K Essmiller]

I have two queries that I need to link together on several fields. I need to
link on a PPOCode that is sometimes null. I need a count of claims for each
PPOCode. I get the count for all of the codes except the null codes. I need
to know how to get it to recognize this link. I also want to know if there is
a setting somewhere in preferences, or somewhere like that, that dictates how
null values are treated. I'm sure the newer versions are handling nulls
differently than they used to or a setting is different.

 

[Jeff Boyce]

I may be missing something here ...

A "null" means that there's no way to know. This isn't the same as a "0"
(nothing, none of, ...).

So how are you proposing to "link" on a field when it (sometimes) contains
"no-way-to-know"? To what would that connect?

The Nz() function gives you a way of converting a null to some other value,
BUT !!! If I tell you there's 0 inches of rainfall, you know there was
none. If I tell you I don't know if it's raining, that's a totally
different matter! Are you sure you want to convert your nulls?


Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[K Essmiller]

OK. What you said makes sense. Perhaps I don't totally understand nulls. Does
Access do something behind the scenes so that nulls display like a zero
length entry? Nothing displays in any of these fields. What I want are the
fields with values to match on like values and the ones with null,
zero-length, or whatever you want to call them to match also. I've just
become aware of the nz() function which looks helpful, but what do you do on
links?

I also want to know if there are setting on how to handle nulls. I am
continually finding more places where Access does wierd and unexpected things
with null values that I don't think it used to to in previous versions.

 

[Jeff Boyce]

Do a bit of research on "nulls" ...

As I understand them (given that there's nothing there to understand <g>),
Nulls indicate nothing known.

As a recovering statistician, "0" (zero) means none-of-whatever, and isn't
the same thing as a null.

And Access includes the concept of a "zero-length-string", designated by two
quotes, nothing between them (""). This is not like EITHER of the two
preceding concepts! You could get one of these if you typed in a name, then
back-spaced until nothing showed. What's left is, I believe, a zls.

Finally, when you look at a field and see nothing, there's always the
possibility that there are any number of blanks/spaces.

You'll want to be testing for/handling each/all of these conditions!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.