Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

Blank Filters - using @@ and other techniques


Sometimes you want to filter an NL function based on the contents of another cell or the output of another NL function, where either of which could be a blank value.

The NL function does not allow you to use blank filters, and you will get a #VALUE error message if you fill in a FilterField parameter and leave the corresponding filter blank.

One example of this is an NL("Table") function which includes user-defined report option.

If the user were to leave the Balance filter blank, an error would result

 

Jet Professional Examples

C4=""

NL (1,"Item","Description","No.",C4)

=IF(C4="","",NL (1,"Item","Description","No.",C4))

=NL(1,"Item","Description","No.",""&C4&"")

=NL (1,"Item","Description","No.","@@"&C4)

In Northwind, an example of this would be in the case of retrieving orders based on the output of an NL that listed postal codes from orders. You could have orders that did not have postal codes so the NL command could return a blank value. This is where you need to use an Excels IF function. Assume that an NL function that lists postal codes is in cell C4. If C4 is blank, you want cell D4 to be blank too. Otherwise, you want to filter an NL in D4 based on the contents of C4.

The first step is to test cell C4 to see if it is blank. Excel will do that for you using the following formula.

C4=""

The formula above uses two double quote characters to represent a blank cell and gives you a True/False response.

The NL function below retrieves the Order ID based on the zip code in C4.

NL(1,"Orders","OrderID","ShipPostalCode",C4)

Combining the two functions above into an IF function gives the following result.

=IF(C4="","", NL(1,"Orders","OrderID"," ShipPostalCode",C4))

The spreadsheet below has the results of the formulas described above.

The example above assumes that blank values are not something you want to find. Sometimes you want to find all records that have a blank value. In this case, you can use two single quotes ('').  In the above example, if C4 could be blank and you wanted to use it as a filter anyway, you could use the following formula.

=NL(1," Orders"," OrderID"," ShipPostalCode",""&C4&"")

Although this formula is a little hard to read, it is putting a single quote character (') on either side of the C4 reference. The & function is an Excel function that combines two bits of text into one so the result of this formula will add a single quote character on either side of the value in cell C4. If C4 contains the Order ID Shampoo1, the resulting filter will be Shampoo1. If C4 is blank, the result is (two single quotes), which is a valid filter for blanks.

An easier to read variation on the filter for blanks is "@@" in front of the value in C4. The function becomes the following.

=NL(1," Orders"," OrderID"," ShipPostalCode","@@"&C4)

 
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments