If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linking on field that allows nulls
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. |
#2
|
|||
|
|||
Linking on field that allows nulls
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" wrote in message ... 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. |
#3
|
|||
|
|||
Linking on field that allows nulls
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" wrote: 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" wrote in message ... 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. . |
#4
|
|||
|
|||
Linking on field that allows nulls
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. "K Essmiller" wrote in message ... 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" wrote: 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" wrote in message ... 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. . |
Thread Tools | |
Display Modes | |
|
|