Show only Null Records (1 Viewer)

[access2010]

Could we please receive help in advising us how to show the NULL records;

If the fields contains Watch and field contains Yes and EITHER of the other 2 fields are null

Your assistance is appreciate.
Nicole

 

[GaP42]

Is this the SQL for the query you need?

SELECT Investments01_tbl.Investmentl_ID, Investments01_tbl.Symbol_Stock, Investments01_tbl.Stock_Name, Investments01_tbl.Sector, Investments01_tbl.[Sub-Industry], Investments01_tbl.Sergey, Investments01_tbl.Investigate
FROM Investments01_tbl
WHERE (((Investments01_tbl.Sergey) Is Null)) OR (((Investments01_tbl.Investigate) Is Null)) OR (((Investments01_tbl.Sergey) Is Null) AND ((Investments01_tbl.Investigate) Is Null));

 

[Pat Hartman]

Are you sure that the "empty" values are actually Null? If the fields are text, they might be Zero Length Strings (ZLS) which are represented as "".

 

[access2010]

Is this the SQL for the query you need?

SELECT Investments01_tbl.Investmentl_ID, Investments01_tbl.Symbol_Stock, Investments01_tbl.Stock_Name, Investments01_tbl.Sector, Investments01_tbl.[Sub-Industry], Investments01_tbl.Sergey, Investments01_tbl.Investigate
FROM Investments01_tbl
WHERE (((Investments01_tbl.Sergey) Is Null)) OR (((Investments01_tbl.Investigate) Is Null)) OR (((Investments01_tbl.Sergey) Is Null) AND ((Investments01_tbl.Investigate) Is Null));

Thank You GaP42 for your suggestion, I am sorry for the misunderstanding
We want to see the records as follows;
Show the SERGEY records that are marked WATCH and the INVESTIGATE records that are marked YES and the SECTOR records that contain no value and the SUB-INDUSTRY records that contain no value.


Question?
Should I refer to the empty records as, Empty, Null or Blank?

Thank you.
Fabiola

 

[access2010]

Are you sure that the "empty" values are actually Null? If the fields are text, they might be Zero Length Strings (ZLS) which are represented as "".

 

[access2010]

Thank you Pat for your question.

Should I refer to the empty records as, Empty, Null or Blank?

Thank you.
Fabiola

 

[GaP42]

Thank You GaP42 for your suggestion, I am sorry for the misunderstanding
We want to see the records as follows;
Show the SERGEY records that are marked WATCH and the INVESTIGATE records that are marked YES and the SECTOR records that contain no value and the SUB-INDUSTRY records that contain no value.


Question?
Should I refer to the empty records as, Empty, Null or Blank?

Thank you.
Fabiola

If I take your statement "Show the SERGEY records that are marked WATCH and the INVESTIGATE records that are marked YES and the SECTOR records that contain no value and the SUB-INDUSTRY records that contain no value." and apply it in a query - no records are returned in your sample db (and a quite trivial query design). So I wondered if you really meant "AND" .. I think you might mean OR for Sector and Sub-Industry, which will return some records:

SELECT Investments01_tbl.Sergey, Investments01_tbl.Investigate, Investments01_tbl.Sector, Investments01_tbl.[Sub-Industry]
FROM Investments01_tbl
WHERE (((Investments01_tbl.Sergey)="watch") AND ((Investments01_tbl.Investigate)="yes") AND ((Investments01_tbl.Sector) Is Null)) OR (((Investments01_tbl.Sergey)="Watch") AND ((Investments01_tbl.Investigate)="Yes") AND ((Investments01_tbl.[Sub-Industry]) Is Null));

The data you supply, which I think is acquired from Excel, for Sector and Sub-Industry are nulls, where nothing is provided.
And since you probably need the record PK (oops)

SELECT Investments01_tbl.Investmentl_ID, Investments01_tbl.Symbol_Stock, Investments01_tbl.Sergey, Investments01_tbl.Investigate, Investments01_tbl.Sector, Investments01_tbl.[Sub-Industry]
FROM Investments01_tbl
WHERE (((Investments01_tbl.Sergey)="watch") AND ((Investments01_tbl.Investigate)="yes") AND ((Investments01_tbl.Sector) Is Null)) OR (((Investments01_tbl.Sergey)="Watch") AND ((Investments01_tbl.Investigate)="Yes") AND ((Investments01_tbl.[Sub-Industry]) Is Null));

 

[Pat Hartman]

I asked the question because if the query doesn't work when it only checks for null, then you must also check for ZLS. My preference is to never allow ZLS in text fields to begin with. The current Access default allows them which I think is wrong because it allows a CompanyName field, for example, to be = "" (which is a ZLS) and therefore logically empty even though you define the CompanyName field as required.

I took out the extraneous parentheses that MS loves to add and we end up with:
WHERE (Investments01_tbl.Sergey = "watch" AND Investments01_tbl.Investigate = "yes" AND Investments01_tbl.Sector Is Null) OR (Investments01_tbl.Sergey = "Watch" AND Investments01_tbl.Investigate = "Yes" AND Investments01_tbl.[Sub-Industry] Is Null);

If the two fields in question allow ZLS, then you can change the where clause to:
WHERE (Investments01_tbl.Sergey = "watch" AND Investments01_tbl.Investigate = "yes" AND Investments01_tbl.Sector & "" = "") OR (Investments01_tbl.Sergey = "Watch" AND Investments01_tbl.Investigate = "Yes" AND Investments01_tbl.[Sub-Industry] & "" = "");

This version concatenates a ZLS with the column and then checks for a ZLS.

 

[access2010]

If I take your statement "Show the SERGEY records that are marked WATCH and the INVESTIGATE records that are marked YES and the SECTOR records that contain no value and the SUB-INDUSTRY records that contain no value." and apply it in a query - no records are returned in your sample db (and a quite trivial query design). So I wondered if you really meant "AND" .. I think you might mean OR for Sector and Sub-Industry, which will return some records:

SELECT Investments01_tbl.Sergey, Investments01_tbl.Investigate, Investments01_tbl.Sector, Investments01_tbl.[Sub-Industry]
FROM Investments01_tbl
WHERE (((Investments01_tbl.Sergey)="watch") AND ((Investments01_tbl.Investigate)="yes") AND ((Investments01_tbl.Sector) Is Null)) OR (((Investments01_tbl.Sergey)="Watch") AND ((Investments01_tbl.Investigate)="Yes") AND ((Investments01_tbl.[Sub-Industry]) Is Null));

The data you supply, which I think is acquired from Excel, for Sector and Sub-Industry are nulls, where nothing is provided.
And since you probably need the record PK (oops)

SELECT Investments01_tbl.Investmentl_ID, Investments01_tbl.Symbol_Stock, Investments01_tbl.Sergey, Investments01_tbl.Investigate, Investments01_tbl.Sector, Investments01_tbl.[Sub-Industry]
FROM Investments01_tbl
WHERE (((Investments01_tbl.Sergey)="watch") AND ((Investments01_tbl.Investigate)="yes") AND ((Investments01_tbl.Sector) Is Null)) OR (((Investments01_tbl.Sergey)="Watch") AND ((Investments01_tbl.Investigate)="Yes") AND ((Investments01_tbl.[Sub-Industry]) Is Null));

Thank you GaP42 for your assistance and we are now getting the results that we wanted.
Crystal

​

 

[access2010]

I asked the question because if the query doesn't work when it only checks for null, then you must also check for ZLS. My preference is to never allow ZLS in text fields to begin with. The current Access default allows them which I think is wrong because it allows a CompanyName field, for example, to be = "" (which is a ZLS) and therefore logically empty even though you define the CompanyName field as required.

I took out the extraneous parentheses that MS loves to add and we end up with:
WHERE (Investments01_tbl.Sergey = "watch" AND Investments01_tbl.Investigate = "yes" AND Investments01_tbl.Sector Is Null) OR (Investments01_tbl.Sergey = "Watch" AND Investments01_tbl.Investigate = "Yes" AND Investments01_tbl.[Sub-Industry] Is Null);

If the two fields in question allow ZLS, then you can change the where clause to:
WHERE (Investments01_tbl.Sergey = "watch" AND Investments01_tbl.Investigate = "yes" AND Investments01_tbl.Sector & "" = "") OR (Investments01_tbl.Sergey = "Watch" AND Investments01_tbl.Investigate = "Yes" AND Investments01_tbl.[Sub-Industry] & "" = "");

This version concatenates a ZLS with the column and then checks for a ZLS.

Thank you Pat Hartman, your comments about ZLS where not known by me previously.
We are obtaining the results which we required and thank you for your assistance.
Crystal

​

 

[Pat Hartman]

You're welcome.