Here's a response I got recently from Epicor for this question:
Â
Summary:Â Â Â Â Â Â Â BAQ How to filter a BAQ on a null or blank field.
Book:Â Â Â Â Â Â Â Â Â Support Solutions
Page:Â Â Â Â Â Â Â Â Â 7290MPS
Page 7290MPS
PROBLEM:
How to compare null values with a BAQ.
How can I filter on a null value with a BAQ?
STEPS TO REPRODUCE:
The easiest way to do this is to create a criteria that compares against a field that is completely empty. Ie, character10 in most cases will contain no data. (any field that has no data in it will work)
To do this:
In this sample query I will use a query that uses part and partmtl. Part.Character01 has some fields that are populated and some that are NULL (empty). This will demonstrate how to get only the fields that are NULL and only the fields that are non null (populated).
1. Create a new query, give it a name and description and then press the phrase builder button.
2. Select the part and partmtl table from the available table columns.
3. Press next.
4. At the table relationship screen press next. (the default relationship will work)
5. At the critieria screen for the Part table press the add new criteria button, and for fieldname select TypeCode, select the "=" operand and check the "is constant" checkbox.Â
6. In the constant text box put the letter M. (we only want manufactured parts).
7. Press the add new criteria button again to add an additional criteria.
8. In the AndOr box select And
9. For Fieldname select Character01. (this is the field we want to compare to the null fie
10. ld). Ie, character 01 potentially has data in it, sometimes it may be null.
11. For the CompOp (operand), select "=".
12. For the ToDataTableID select the Part table. In the ToFieldName select Character10.
Character 10 is the field that will always contain a NULL value in this case.
13. Press Finished.
14. Select which fields you want to display in the display tab, and then go the analyze tab and press test.
15. This will show all records that Character01 has a NULL value for.
If we wanted to display all fields that are NOT NULL, in step 10 you change the operand from = to <>. ("<>" means NOT EQUAL).
The exported query example is attached to this page.
<file:nullFilterQuery.xml>
Additional Notes:
1) Please note the above example is does not use an outerjoin.
2) the data in the character10 field is not initialized to NULL, but contain empty results --> ''.Â
3) the data in the userdate01 field is initialized to NULL.
4) be sure to select a user defined field that is appropiate for the type of control e.g.
   use Character10 for a character field, use Number10 for a numeric field.
5) on a date field ISDateNull maybe used.
Â
Ahmet
Â
Summary:Â Â Â Â Â Â Â BAQ How to filter a BAQ on a null or blank field.
Book:Â Â Â Â Â Â Â Â Â Support Solutions
Page:Â Â Â Â Â Â Â Â Â 7290MPS
Page 7290MPS
PROBLEM:
How to compare null values with a BAQ.
How can I filter on a null value with a BAQ?
STEPS TO REPRODUCE:
The easiest way to do this is to create a criteria that compares against a field that is completely empty. Ie, character10 in most cases will contain no data. (any field that has no data in it will work)
To do this:
In this sample query I will use a query that uses part and partmtl. Part.Character01 has some fields that are populated and some that are NULL (empty). This will demonstrate how to get only the fields that are NULL and only the fields that are non null (populated).
1. Create a new query, give it a name and description and then press the phrase builder button.
2. Select the part and partmtl table from the available table columns.
3. Press next.
4. At the table relationship screen press next. (the default relationship will work)
5. At the critieria screen for the Part table press the add new criteria button, and for fieldname select TypeCode, select the "=" operand and check the "is constant" checkbox.Â
6. In the constant text box put the letter M. (we only want manufactured parts).
7. Press the add new criteria button again to add an additional criteria.
8. In the AndOr box select And
9. For Fieldname select Character01. (this is the field we want to compare to the null fie
10. ld). Ie, character 01 potentially has data in it, sometimes it may be null.
11. For the CompOp (operand), select "=".
12. For the ToDataTableID select the Part table. In the ToFieldName select Character10.
Character 10 is the field that will always contain a NULL value in this case.
13. Press Finished.
14. Select which fields you want to display in the display tab, and then go the analyze tab and press test.
15. This will show all records that Character01 has a NULL value for.
If we wanted to display all fields that are NOT NULL, in step 10 you change the operand from = to <>. ("<>" means NOT EQUAL).
The exported query example is attached to this page.
<file:nullFilterQuery.xml>
Additional Notes:
1) Please note the above example is does not use an outerjoin.
2) the data in the character10 field is not initialized to NULL, but contain empty results --> ''.Â
3) the data in the userdate01 field is initialized to NULL.
4) be sure to select a user defined field that is appropiate for the type of control e.g.
   use Character10 for a character field, use Number10 for a numeric field.
5) on a date field ISDateNull maybe used.
Â
Ahmet
--- On Mon, 10/13/08, Scott Litzau <scott.litzau@...> wrote:
From: Scott Litzau <scott.litzau@...>
Subject: RE: [Vantage] V8.03 - NULLS in BAQs
To: vantage@yahoogroups.com
Date: Monday, October 13, 2008, 4:28 PM
Joe,
Did you ever get an answer. I want to use IsNull in a BAQ to see if there is
Open Orders with no Order Lines.
Scott
-----Original Message-----
From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf Of
Rojas, Joe
Sent: Thursday, October 02, 2008 12:44 PM
To: vantage@yahoogroups .com
Subject: [Vantage] V8.03 - NULLS in BAQs
Hello,
I think this topic has been brought up before but I need to create a BAQ
that checks for NULLs as a criteria.
I need to link the Part table to the PartTran table. I only want to see
PartTran records of a certain TranType but I also want the BAQ to return
all records from the Part table.
Once I put a criteria on the PartTran table, the query will start to act
like an INNER join even though it's flagged as an OUTER.
If I could put a criteria on the PartTran table that was something like
TranType = NULL that should address the problem.
Is this possible?
Joe Rojas
Information Technology Manager
Symmetry Medical New Bedford
P: 508.998.4575
M:508.838.4717
F:508.995.9597
joe.rojas@symmetryn b.com <mailto:joe.rojas@symmetryn b.com>
[Non-text portions of this message have been removed]
------------ --------- --------- ------
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups. yahoo.com/ group/vantage/ files/.
(2) To search through old msg's goto:
http://groups. yahoo.com/ group/vantage/ messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups. yahoo.com/ group/vantage/ linksYahoo! Groups Links
[Non-text portions of this message have been removed]