Null Values

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
S

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
 
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.
 
Stacey said:
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;
Click to expand...


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,"") = ""
 
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?

Jerry Whittle said:
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.


Stacey Crowhurst said:
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
Click to expand...
Click to expand...
 
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!
 
Stacey said:
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.
Click to expand...


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.
 
Back
Top