LEFT JOIN returns a single space instead of NULL

[ifiaz]

I have the following query:

---
SELECT [qry03_DayRoster].StaffNo, [qry03_DayRoster].StaffName,
[qry03_DayRoster].DutyTime, [qry03_DayRoster].E_UniqueName,
[qry02_ActiveStaffNonMgmt].E_UniqueName

FROM qry03_DayRoster LEFT JOIN qry02_ActiveStaffNonMgmt

ON
[qry03_DayRoster].E_UniqueName=[qry02_ActiveStaffNonMgmt].E_UniqueName;
---

Both [qry03_DayRoster].E_UniqueName and
[qry02_ActiveStaffNonMgmt].E_UniqueName
is a calculated expression that concatenates the StaffNumber and
StaffName like this:

E_UniqueName: [StaffNo] & LCase(Left([E_FullName],2))
e.g. a returned value might be "577777fi"

[qry02_ActiveStaffNonMgmt].E_UniqueName is supposed to return NULL when
matching E_UniqueName can't be found on [qry03_DayRoster].E_UniqueName.
But, it returns a single space " " for those NULL values instead. This
causes confusion for me.

Is there an explanation for this?

Am I clear enough with the problem statement?

Please reply.

 

[Amy Blankenship]

I have the following query:

---
SELECT [qry03_DayRoster].StaffNo, [qry03_DayRoster].StaffName,
[qry03_DayRoster].DutyTime, [qry03_DayRoster].E_UniqueName,
[qry02_ActiveStaffNonMgmt].E_UniqueName

FROM qry03_DayRoster LEFT JOIN qry02_ActiveStaffNonMgmt

ON
[qry03_DayRoster].E_UniqueName=[qry02_ActiveStaffNonMgmt].E_UniqueName;
---

Both [qry03_DayRoster].E_UniqueName and
[qry02_ActiveStaffNonMgmt].E_UniqueName
is a calculated expression that concatenates the StaffNumber and
StaffName like this:

E_UniqueName: [StaffNo] & LCase(Left([E_FullName],2))
e.g. a returned value might be "577777fi"

[qry02_ActiveStaffNonMgmt].E_UniqueName is supposed to return NULL when
matching E_UniqueName can't be found on [qry03_DayRoster].E_UniqueName.
But, it returns a single space " " for those NULL values instead. This
causes confusion for me.

Left([E_FullName],2) should actually be returing two spaces, rather than
one.

HTH;

Amy

 

[ifiaz]

Left([E_FullName],2) will NEVER return two spaces or even one.

Left([E_FullName],2) will ALWAYS have a value that has two alphabets
e.g. "fi", "ab", etc.

E_UniqueName will ALWAYS have a value that has 8 characters e.g.
"577777fi", "577778ab", etc.

So, why is there a single space " " that appear when what I expect is a
NULL value for the "unmatched query".

 

[Douglas J. Steele]

Why do you say that Left([E_FullName],2) will NEVER return two spaces or
even one?

If E_FullName contains " Steele", then yes, Left([E_FullName],2) would
return " ", as that was the first two positions of the field.

However, that's probably incidental to your problem. How are you determining
that you're getting spaces rather than Nulls in your join?

What happens if you (temporarily) change your query to:

SELECT [qry03_DayRoster].StaffNo, [qry03_DayRoster].StaffName,
[qry03_DayRoster].DutyTime, [qry03_DayRoster].E_UniqueName,
[qry02_ActiveStaffNonMgmt].E_UniqueName
FROM qry03_DayRoster LEFT JOIN qry02_ActiveStaffNonMgmt
ON
[qry03_DayRoster].E_UniqueName=[qry02_ActiveStaffNonMgmt].E_UniqueName;
WHERE [qry02_ActiveStaffNonMgmt].E_UniqueName IS NULL

Do you get the expected values returned?

 

[ifiaz]

Thanks for your reply.

E_FullName NEVER contain leading spaces. I have only about 312 records
and all of them has the name correctly.

Also, I have run your query and what I get returned is an EMPTY
recordset.

However when I removed the criteria IS NULL out, It returns all the
recordset of qry03_DayRoster but the unmatched field in
qry02_ActiveStaffNonMgmt.E_UniqueName contains a single " " instead of
NULL.

Please see the record 2 and 5 below for example.

Query Result:

StaffNo,StaffName,DutyTime,qry03_DayRoster.E_UniqueName,qry02_ActiveStaffNonMgmt.E_UniqueName
701172,LAI F,1200 - 2100,701172la,701172la
701173,LEE W,DAY - OFF,701173le,
701179,WONG S,DAY - OFF,701179wo,701179wo
701183,CHAN W,1600 - 0100,701183ch,701183ch
701184,TAM S,0530 - 1430,701184ta,

---

Additional Info:
If I do LEFT JOIN equality on an actual field name like StaffNo or
StaffName the returned value is NULL for the unmatched query. Only when
I use the equality for the LEFT JOIN on calculated expression like
E_UniqueName it returns a single space " ".

 

[Douglas J. Steele]

Try posting the complete SQL for qry03_DayRoster and
qry02_ActiveStaffNonMgmt.

And, as a favour (since you trim far too much from each post!), repost your
original query

 

[ifiaz]

I have the following queries:

1) qry03_DayRoster:

SELECT tblDayRoster.*, [StaffNo] & LCase(Left([StaffName],2)) AS
E_UniqueName
FROM tblDayRoster;

Sample result:
StaffNo StaffName DutyTime CellA1 CellA2 CellB1 CellC1 SheetName E_UniqueName
553862 SHIN 1945 - 2345 P LBA(3) 553862sh
553881 JAM 0700 - 1600 LBA(3) 553881ja

2) qry02_ActveStaffNonMgmt:

SELECT tblStaffList.*, tblPosition.*, [NameLast] & " " & [NameMiddle] &
(" "+[NameFirst]) & (", "+[NameEnglish]) AS E_FullName, [StaffNo] &
LCase(Left([E_FullName],2)) AS E_UniqueName
FROM tblPosition INNER JOIN tblStaffList ON tblPosition.Position =
tblStaffList.Position
ORDER BY tblStaffList.StaffNo;

Sample Result:
StaffNo NameLast NameMiddle NameFirst E_FullName E_UniqueName
526772 Liu Yuk Ho Liu Yuk Ho, Winnie 526772li
553862 Shin Sing Chow Shin Sing Chow 553862sh

3) qry99_DayRoster Without Matching qry02_ActiveStaffOnRoster:

SELECT qry03_DayRoster.StaffNo, qry03_DayRoster.StaffName,
qry03_DayRoster.DutyTime, qry03_DayRoster.E_UniqueName,
qry02_ActiveStaffNonMgmt.E_UniqueName
FROM qry03_DayRoster LEFT JOIN qry02_ActiveStaffNonMgmt
ON qry03_DayRoster.E_UniqueName=qry02_ActiveStaffNonMgmt.E_UniqueName;

Sample Result:

StaffNo StaffName DutyTime qry03_DayRoster.E_UniqueName qry02_ActiveStaffNonMgmt.E_UniqueName
575475 NG 1430 - 2330 575475ng 575475ng
577501 LI DAY OFF 577501li
577663 CHAN SH-02 577663ch 577663ch

Problem:

In the above query

Both [qry03_DayRoster].E_UniqueName and
[qry02_ActiveStaffNonMgmt].E_UniqueName
is a calculated expression that concatenates the StaffNumber and
StaffName like this:

E_UniqueName: [StaffNo] & LCase(Left([E_FullName],2))
e.g. a returned value might be "577777fi"

[qry02_ActiveStaffNonMgmt].E_UniqueName is supposed to return NULL when

matching E_UniqueName can't be found on [qry03_DayRoster].E_UniqueName.

But, it returns a single space " " for those NULL values instead. This
causes confusion for me.

Is there an explanation for this?

Am I clear enough with the problem statement?

Please reply.

 

[Douglas J. Steele]

See whether using +, rather than &, in your concatenations makes a
difference.

Null & something results in Something, whereas Null + something results in
Null.

 

[ifiaz]

Yes.

After changing the query 1) qry03_DayRoster: and 2)
qry02_ActveStaffNonMgmt:
from
[StaffNo] & LCase(Left([E_FullName],2)) AS E_UniqueName
to
[StaffNo] + LCase(Left([E_FullName],2)) AS E_UniqueName

and that was the only change I made.

It works as expected.

Now, [qry02_ActiveStaffNonMgmt].E_UniqueName returns a NULL instead of
a single space.

Thank you so much.