# BAQ Filter for Null **Category:** [Epicor ERP 10](https://www.epiusers.help/c/e10/5) **Created:** 2017-02-13 13:38 UTC **Views:** 9990 **Replies:** 13 **URL:** https://www.epiusers.help/t/baq-filter-for-null/37256 --- ## Post #1 by @DaveOlender I am trying to write a BAQ that filters on a Null and I cant seem to figure out how to do this. I would like to show only the JobOper records where my open jobs do NOT have any JobOpers. I have a main BAQ that lists all of my Open Jobs. I then added the JobOper table and did a left join. I see the blank fields from JobOper where there are no operations - However I would like to filter my main BAQ so the only thing showing is the Jobs that have NO JobOper Records. Am I over thinking this? Any Help greatly appreciated. Thanks, DaveO Ph: 651-246-3281 --- ## Post #2 by @DaveOlender Here is a screenshot:

--- ## Post #3 by @BKoch In Table Criteria, under the operation where it defaults to '=' there is an option for 'ISNULL' that you can then apply to your field, whether it be the Operation or the Opr column. --- ## Post #4 by @T11 You could add a calculated field to the BAQ that conditionally shows a value and filter on that field --- ## Post #5 by @DaveOlender Mr. Brian: I see the ISNULL however - when I add that as a table criteria - It does not work correctly. I should see just 11 records and when I add the ISNULL condition to the JobSeq field on the JobOper Table, I get 559 records. I must be doing this wrong. --- ## Post #6 by @DaveOlender Mr. Tanner: Thank you for the suggestion. I was able to create a calculated field that shows the right answer - however I don't see my calculated field in the list of fields available for table criteria. I suppose if I was going to use this BAQ in a dashboard I could apply a filter on my calculated field - however I don't plan on using this BAQ in a dashboard. Any other suggestions appreciated - maybe use a subreport somehow? DaveO --- ## Post #7 by @markdamen You can add it to Subquery Criteria, which applies to the returned results. You can’t add it as a Table Criteria because it’s not a table field. Regards Mark --- ## Post #8 by @T11 Dave, It appears you would need to use a subquery and filter the criteria on the subquery. There is a feature request on the EUG board to add the ability to use SQL in BAQ designer which would make this much simpler. Thanks, Tanner --- ## Post #9 by @knash What does the query look like on the general tab? That helps with debugging. Then you don't have to mess with a calculated field. --- ## Post #10 by @DaveOlender

--- ## Post #11 by @knash You are close. I only added the following subquery criteria and the left join from the header. Give this a try.

--- ## Post #12 by @DaveOlender Thank you Mr. Nash: That did the trick. Use SubQuery condition (even though i technically do NOT have a subquery) and it works perfectly. No Calculated field needed. You are the BAQ MAN! Thanks again, DaveO --- ## Post #13 by @Matt_Belshaw Just added to this thread as the solution from Ken didn't work for me. I had to a criteria, and not a sub one (although I'm taking note of that solution for future use), to say FIELD <> constant and then when the pop up box came up saying what's the constant I didn't type anything and just pressed ok. The BAQ screen then had "Empty string" as the constant. Then the only result I got back where when we had typed something into that field: ![image|690x111](upload://915Wca0g8PPO0FV2CiIPH8c42wz.png) I think I remember conversations with DBAs and what really is NULL. I suppose it depends on what solution will work depending on what Epicor state the DB table structure to be. --- ## Post #14 by @fredmeissner This helped me, thank you! --- **Canonical:** https://www.epiusers.help/t/baq-filter-for-null/37256 **Original content:** https://www.epiusers.help/t/baq-filter-for-null/37256