Dont' display rows based on certain columns being empty?

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,218
Hey All

I've built a Query that pulls in details about certain products in our range.

The problem I've got is that the details for each product live in a text file, it contains details for every product we've ever stocked including some obsolete ones.

Because of this my Query is returning lots of blank rows when there is no SKU associated any more.

The SKUs end up in one of 6 columns at the end of the data.

I thought I'd be able to hide them by using another column to see if they're blank or not and the using a criteria filter.

Code:
Good: IIf(IsNull([Level 0 Live SKU]) And IsNull([Level 1 Live SKU]) And IsNull([Level 2 Live SKU]) And IsNull([Level 3 Live SKU]) And IsNull([Level 4 Live SKU]) And IsNull([Level 5 Live SKU]),"N","Y")
The formula works if I just run the query and the new column populates correctly but when I include the <>"N" criteria I get a "Enter Parameter Value" pop up message box for each of the Level (x) Live SKU fields.

Can I not do it this way? Or am I doing something wrong?

Thanks in advance for any help that can be provided :)
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
279
Office Version
365, 2016
Platform
Windows
Your criteria should be in the Criteria row (or WHERE clause if writing the SQL).

The SQL would be:
Code:
 WHERE Nz([SKU],'') <> ''
The criteria should be able to go under any of the columns in the Criteria row and will look like the SQL minus the WHERE, so:
Code:
 Nz([SKU],'') <> ''
 

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,218
Hi Jon, thanks for response.

"Your criteria should be in the Criteria row" is what I needed :)

I added "Is Not Null" to each column Criteria / Or and that worked.

Thanks for that.
 

Forum statistics

Threads
1,085,435
Messages
5,383,649
Members
401,845
Latest member
ZmutLarr

Some videos you may like

This Week's Hot Topics

Top