Null Values

[Stacey Crowhurst]

Hi. In my query for criteria on a text field [tdContractID] I get 20
responses when I use (Is Null) and 162 responses when I use (""). Why is
Access treating these empty values differently? Here is my SQL:

SELECT tblTransactionDetail.tdCCPID, tblTransactionDetail.tdCostCodeID,
tblTransactionDetail.tdPhaseCodeID, tblTransactionDetail.tdVendorID,
tblVendors.vdVendorName, Sum(tblTransactionDetail.tdLineAmount) AS
NonContractCosts
FROM qryProjectMaxSequence INNER JOIN (tblProjects INNER JOIN
((tblTransactions INNER JOIN tblTransactionDetail ON
tblTransactions.trAutoNumberID = tblTransactionDetail.tdtrAutoNumberID) LEFT
JOIN tblVendors ON tblTransactionDetail.tdVendorID = tblVendors.vdVendorID)
ON tblProjects.prjCCPID = tblTransactionDetail.tdCCPID) ON
(qryProjectMaxSequence.MaxOfprjSequence = tblProjects.prjSequence) AND
(qryProjectMaxSequence.prjCCPID = tblProjects.prjCCPID)
WHERE (((tblTransactionDetail.tdContractID) Is Null Or
(tblTransactionDetail.tdContractID)=""))
GROUP BY tblTransactionDetail.tdCCPID, tblTransactionDetail.tdCostCodeID,
tblTransactionDetail.tdPhaseCodeID, tblTransactionDetail.tdVendorID,
tblVendors.vdVendorName;

Thank you! Stacey

 

[Jerry Whittle]

Because you actually have 182 fields where it looks like nothing is in them.
Actually you can have even more as nothing but spaces will also not show up.
Check for Like " " & "*"

Actually that will return leading spaces also, but you get the idea.

Nulls and empty strings "" are technically not the same thing. Nulls are
unknown whereas an empty string means nothing. For example if you have a
field for eye color but didn't know my eye color, you would leave it null. If
there was a field for Make of Car, but you knew that I didn't own a car,
technically it would be an empty string. Most people would leave it null
instead, and that's alright with me.

I find that empty strings mostly happen when importing data from other
sources. If this is the case, you really need to test for nulls, empty
strings, and even spaces to make sure that the "blanks" are found.

 

[Marshall Barton]

Hi. In my query for criteria on a text field [tdContractID] I get 20
responses when I use (Is Null) and 162 responses when I use (""). Why is
Access treating these empty values differently? Here is my SQL:

SELECT tblTransactionDetail.tdCCPID, tblTransactionDetail.tdCostCodeID,
tblTransactionDetail.tdPhaseCodeID, tblTransactionDetail.tdVendorID,
tblVendors.vdVendorName, Sum(tblTransactionDetail.tdLineAmount) AS
NonContractCosts
FROM qryProjectMaxSequence INNER JOIN (tblProjects INNER JOIN
((tblTransactions INNER JOIN tblTransactionDetail ON
tblTransactions.trAutoNumberID = tblTransactionDetail.tdtrAutoNumberID) LEFT
JOIN tblVendors ON tblTransactionDetail.tdVendorID = tblVendors.vdVendorID)
ON tblProjects.prjCCPID = tblTransactionDetail.tdCCPID) ON
(qryProjectMaxSequence.MaxOfprjSequence = tblProjects.prjSequence) AND
(qryProjectMaxSequence.prjCCPID = tblProjects.prjCCPID)
WHERE (((tblTransactionDetail.tdContractID) Is Null Or
(tblTransactionDetail.tdContractID)=""))
GROUP BY tblTransactionDetail.tdCCPID, tblTransactionDetail.tdCostCodeID,
tblTransactionDetail.tdPhaseCodeID, tblTransactionDetail.tdVendorID,
tblVendors.vdVendorName;

It treats them differently because they are different. They
may look the same when displayed in a text box on a form or
report. but they have different meanings.

"" is a known string value with zero characters (AKA Zero
Length String or ZLS).

OTOH, Null indicates that the value is Unknown.

They may look the same when displayed in a text box on a
form or report, but that's as far as the similarities go.
The Text field in its table should have its AllowZeroLength
property set to No so the field can not be set to "". If
you are too far down the road to change that or if you have
a real need to allow zero length strings, you have to check
fo both cases as you have or you can check for both cases by
using:
WHERE Nz(tblTransactionDetail.tdContractID,"") = ""

 

[Stacey Crowhurst]

Thank you for the explanation. The problem I have is that the query will
show two rows of "identical" data (one with "" and one null) instead of
summing the results.

tdCCPID tdCostCodeID tdVendorID vdVendorName NonContractCosts
TCC09-02 AMC-CC N/A_135 AMERICAN COUNTRY $604.44
TCC09-02 AMC-CC N/A_135 AMERICAN COUNTRY $5,855.80

What I want to see is
tdCCPID tdCostCodeID tdVendorID vdVendorName NonContractCosts
TCC09-02 AMC-CC N/A_135 AMERICAN COUNTRY $6460.24

How can I go through and change my empty strings to be nulls or vice versa
so that I don't have this grouping problem?

Because you actually have 182 fields where it looks like nothing is in them.
Actually you can have even more as nothing but spaces will also not show up.
Check for Like " " & "*"

Actually that will return leading spaces also, but you get the idea.

Nulls and empty strings "" are technically not the same thing. Nulls are
unknown whereas an empty string means nothing. For example if you have a
field for eye color but didn't know my eye color, you would leave it null. If
there was a field for Make of Car, but you knew that I didn't own a car,
technically it would be an empty string. Most people would leave it null
instead, and that's alright with me.

I find that empty strings mostly happen when importing data from other
sources. If this is the case, you really need to test for nulls, empty
strings, and even spaces to make sure that the "blanks" are found.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi. In my query for criteria on a text field [tdContractID] I get 20
responses when I use (Is Null) and 162 responses when I use (""). Why is
Access treating these empty values differently? Here is my SQL:

SELECT tblTransactionDetail.tdCCPID, tblTransactionDetail.tdCostCodeID,
tblTransactionDetail.tdPhaseCodeID, tblTransactionDetail.tdVendorID,
tblVendors.vdVendorName, Sum(tblTransactionDetail.tdLineAmount) AS
NonContractCosts
FROM qryProjectMaxSequence INNER JOIN (tblProjects INNER JOIN
((tblTransactions INNER JOIN tblTransactionDetail ON
tblTransactions.trAutoNumberID = tblTransactionDetail.tdtrAutoNumberID) LEFT
JOIN tblVendors ON tblTransactionDetail.tdVendorID = tblVendors.vdVendorID)
ON tblProjects.prjCCPID = tblTransactionDetail.tdCCPID) ON
(qryProjectMaxSequence.MaxOfprjSequence = tblProjects.prjSequence) AND
(qryProjectMaxSequence.prjCCPID = tblProjects.prjCCPID)
WHERE (((tblTransactionDetail.tdContractID) Is Null Or
(tblTransactionDetail.tdContractID)=""))
GROUP BY tblTransactionDetail.tdCCPID, tblTransactionDetail.tdCostCodeID,
tblTransactionDetail.tdPhaseCodeID, tblTransactionDetail.tdVendorID,
tblVendors.vdVendorName;

Thank you! Stacey

 

[KenSheridan via AccessMonster.com]

Stacey:

As you are not including the tdContractID column in the GROUP BY clause
whether it contains a zero-length string or a Null should make no difference
to the grouping. It looks to me like there must be different values in the
tdPhaseCodeID column as this is included in the GROUP BY clause, but while it
is included in the SELECT clause, you haven't shown it in the sample results
which you've posted. Removing the tdPhaseCodeID column from the SELECT and
GROUP BY clauses should cure it.

Notwithstanding that I would suggest that for consistency you update the
table to convert the zero-length strings to Nulls with:

UPDATE tblTransactionDetail
SET tdContractID = NULL
WHERE tdContractID = "";

Or if you want to be really purist update it to something like N/A with:

UPDATE tblTransactionDetail
SET tdContractID = "N/A"
WHERE NZ(tdContractID,"") = "";

That will remove the 'semantic ambiguity' of the Nulls, i.e. the values in
the relevant rows will be telling you that tdContractID is not applicable to
this particular transaction, rather than 'unknown' or 'maybe' which is about
as close as you can get to saying what a Null means.

Then set the AllowZeroLength property of the tdContractID column to False
(No) in table design view. If you've opted for the N/A update you can if you
wish also set the DefaultValue property of the tdContractID column to "N/A"
so that that value will be entered by default in any new record, to be
overwritten when appropriate. You'd then test for WHERE tdContractID ="N/A"
in the query of course.

Ken Sheridan
Stafford, England

Thank you for the explanation. The problem I have is that the query will
show two rows of "identical" data (one with "" and one null) instead of
summing the results.

tdCCPID tdCostCodeID tdVendorID vdVendorName NonContractCosts
TCC09-02 AMC-CC N/A_135 AMERICAN COUNTRY $604.44
TCC09-02 AMC-CC N/A_135 AMERICAN COUNTRY $5,855.80

What I want to see is
tdCCPID tdCostCodeID tdVendorID vdVendorName NonContractCosts
TCC09-02 AMC-CC N/A_135 AMERICAN COUNTRY $6460.24

How can I go through and change my empty strings to be nulls or vice versa
so that I don't have this grouping problem?

Because you actually have 182 fields where it looks like nothing is in them.
Actually you can have even more as nothing but spaces will also not show up.

[quoted text clipped - 35 lines]

 

[Stacey Crowhurst]

Thank you all for your help. I did Ken's suggestion and ran an update query
for tdContractID and tdPhaseCodeID to change any "" into NULL. Now the
grouping works great. Thank you again!

 

[Marshall Barton]

Thank you all for your help. I did Ken's suggestion and ran an update query
for tdContractID and tdPhaseCodeID to change any "" into NULL. Now the
grouping works great.

In that case, you should seriously think about setting the
field's AllowZeroLenght property to No so more ZLS values
don't creep back in some new or edited records.