Avatar of Liberty4all
Liberty4all
Flag for United States of America asked on

Do null values adversely affect join properties in Access 2010?

I have a query that joins two tables using three fields in Access 2010.  The fields used in the inner joins have identical data types.  When joining to only two of the three fields the query returns five records but when adding the third join nothing is returned.  Of the five records returned, only one is populated for the field CommitteeName in one table and the rest are null.  I can understand why that record is not returned when adding a third join but don't understand why the other four should be excluded.  The data type of the field in both tables is text and Allow Zero Length = Yes.  

I ran the following query to replace all blanks with null in both tables thinking this would solve it but no change in results occurred.
UPDATE tblBoardCommitteeImport1F SET tblBoardCommitteeImport1F.CommitteeName = Null
WHERE (((Nz([tblBoardCommitteeImport1F].[CommitteeName],''))=''));

I'm guessing the problem has to do with null values in the CommitteeName field in one or both tables but am not sure what to try next.  Any advice on how to troubleshoot this further would be appreciated.
Microsoft AccessRESTSQL

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
Pawan Kumar

Pls try this -

UPDATE tblBoardCommitteeImport1F
SET tblBoardCommitteeImport1F.CommitteeName = NULL
WHERE LENGTH(tblBoardCommitteeImport1F.CommitteeName) = 0
Pawan Kumar

Edited my last comment.
Liberty4all

ASKER
I get "Undefined function 'Length' in expression" when trying the above.  When I change LENGTH to LEN no records are updated.  What should I try next?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
PatHartman

Null and ZLS are different values.  ZLS is represented as "" and Null is represented as Null.  If you want to determine if a field is Null, you MUST use the IsNull() function in VBA and in SQL you would use "Is Null"  Or the IsNull() function.

Null never equals null so examine the following expressions.  Both will return false when a and b are both null and also when EITHER a or b is false.
If a = b then
    msgbox "True"
Else
    msgbox "False"
End If
If a <> b then
    msgbox "True"
Else
    msgbox "False"
End If

If you need to join on a column that might be empty, then you will have to set allow ZLS to Yes, set the default to "" and in your Form's BeforeUpdate event make sure that null is replaced with ZLS if necessary.  So your update query did exactly the opposite of what it needed to do.  It set ZLS to nulls rather than setting Null to ZLS.

But, the reality is that "key" fields should NEVER be null.  That means you actually have a design flaw and need to create additional tables.  Hopefully only one of the three columns can legitimately be null and that is the "lowest" in the hierarchy.  Split the data associated with that third field into a separate table and since it is optional, always use a Left join in your queries.
Ryan Chong

I'm guessing the problem has to do with null values in the CommitteeName field in one or both tables but am not sure what to try next.
exactly, or you may consider to convert the null in your joining condition, which you can do that by applying NZ function, like:

 AND ( nz(Table1.field,'') = nz(Table2.field,'')) 

Open in new window

PatHartman

There is a typo in my previous comment but I couldn't fix it because Ryan had already posted.

Null never equals null so examine the following expressions.  Both will return false when a and b are both null and also when EITHER a or b is false.
should be

Null never equals null so examine the following expressions.  Both will return false when a and b are both null and also when EITHER a or b is Null.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PatHartman

Do NOT convert fields on the fly in the join clause of the query.  This will make the join not sargeable.  Fix the data (and KEEP it fixed as I suggested by making the field required)  if you decide not to take my advice to fix the underlying problem.
Ryan Chong

When joining to only two of the three fields the query returns five records but when adding the third join nothing is returned.
it also depends on your joining conditions and what data to be extracted from your Select statement. You may consider to use different types of Joining clause, like Left join to examine your SQL results.
Liberty4all

ASKER
Ryan - please tell me how to modify my query syntax to incorporate your suggestion:
AND ( nz(Table1.field,'') = nz(Table2.field,''))
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Pawan Kumar

This way is should be fine.

AND Table1.field = Table2.field  AND ( Table1.field IS NULL OR Table2.field IS NULL )

Open in new window

Ryan Chong

please tell me how to modify my query syntax to incorporate your suggestion:
in general, if you're familiar with this.. you can edit your Query by go to SQL view and then adding the additional condition(s) there.

Or you may post your Select SQL and we will try to give you suggestions then.
Liberty4all

ASKER
I started with this:
UPDATE tblBoardCommitteeImport1F
SET tblBoardCommitteeImport1F.CommitteeName = NULL
WHERE LENGTH(tblBoardCommitteeImport1F.CommitteeName) = 0

I'm content to update one table at a time.  I don't know how to add your suggestion: AND Table1.field = Table2.field  AND ( Table1.field IS NULL OR Table2.field IS NULL ) to the above since my syntax refers to only one table and yours to two.  If I need to use both tables in the query I can do that but please give me the complete syntax.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ryan Chong

ok, so you have updated your field: CommitteeName in table: tblBoardCommitteeImport1F to null if it's zero length.

so, will it help when joining  the 3rd condition?
Pawan Kumar

After running below
UPDATE tblBoardCommitteeImport1F 
SET tblBoardCommitteeImport1F.CommitteeName = NULL
WHERE LEN(tblBoardCommitteeImport1F.CommitteeName) = 0

Open in new window

Did u get the NULL values in your table.
PatHartman

Liberty4all,
Are you trying to join null to null OR null to everything?

If you reread my original post, I tell you that you cannot join null to null and you can see by the path the others are taking you down that I am correct.  So if you really want to join "null" to "null", then convert null to ZLS - ON WHICH YOU CAN JOIN without any problem because ZLS is something whereas Null is nothing.  You just have to make sure to never allow nulls to creep back in.  

If you want to join null to everything,  you will need to do it with criteria and you will almost certainly end up with a Cartesian Product so test carefully.

The best solution always comes down to fixing the design problem which in this case dictates an additional table for the "optional" data.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Liberty4all

ASKER
Pat,

I appreciate your additional comments.  I've never encountered data like this until recently so I'm totally perplexed by what is happening.  Your explanation on why null is different than a zero length string (ZLS) is helpful.  Let me explain more of what I'm doing to add context to our discussion.

I'm importing data from Excel that is produced automatically by a system over which I have no control.  After importing this data is compared to an existing table by joining on three fields.  The field that is causing the problem is named CommitteeName.  Both the imported data and existing (main) table have the same properties for the CommitteeName field: data type is text and Allow Zero Length = Yes.  On the surface this appears to be fine but I then discover the following.

After importing I find all 501 records when WHERE is set to IS NOT NULL for the CommitteeName field but the majority of these are blank.  When WHERE is set to IS NULL no records are returned.  This is not what I was expecting given my lack of experience with ZLS.  Using your suggestion I then set WHERE = "" and get 359 records which are blank and 142 that are populated with WHERE = NOT "".  359 + 142 = 501.

I need to import all records regardless of whether the CommitteeName field is populated.  Often times it will not be but that is expected.  I can learn to use "" instead of Null when checking for blank values.  What is most important, however, is my inner joins work as expected.  Do I need to use WHERE NOT "" or something else in the query that checks for records that match in both tables?  A slimmed down version of this query is shown below.

SELECT tblBoardCommitteeImport1F.[Donor ID], tblBoardCommitteeImport1F.CommitteeName, tblBoardCommitteeImport1F.[Legal Name] INTO tblDupBoardCommittee1
FROM tblBoardCommitteeImport1F INNER JOIN [main table] ON (tblBoardCommitteeImport1F.[Donor ID] = [main table].EmpID) AND (tblBoardCommitteeImport1F.[Legal Name] = [main table].[Org or Project Name]) AND (tblBoardCommitteeImport1F.[CommitteeName] = [main table].CommitteeName)
ORDER BY tblBoardCommitteeImport1F.[Donor ID];

Please advise what changes you recommend in the above query or anything else to insure that I'm getting the desired result.  Thanks.
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Liberty4all

ASKER
Your two sentences shown below are key to fixing my problem.
"In my databases I NEVER allow ZLS except if I have to import into a temp table from an external source.  When I append from the temp table to the actual tables, I convert any ZLS to null."  I'm modifying my process to match your advice because I agree with it.  I'm still encountering things that don't make sense but am finding ways to work around them.  When finished I will close the question but am keeping it open until then in case something I can't fix pops up.
Liberty4all

ASKER
Pat,

Thanks for taking the extra time to respond to my question as that provided the solution I needed.  As noted above your comments about never allowing ZLS values except when importing to a temp table is identical to my situation.  Now that I know how to identify ZLS values using double quotes ("") and understand why IS NOT NULL can include blank records due to ZLS values your advice is tailored exactly to my needs.  Thanks for saying it very clearly.  

Bryan
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PatHartman

Been there and have the scars to prove it.  You're welcome :)