Records with Null Fields not Showing

[dnboughton]

CR8.5
Oracle DB


I've created a simple report, using the select expert. The select expert formula shows:

not ({WORKORDER.EQNUM} in "19375", "19376", "19382", "19378", "19381", "19373", "19370", "19368", "19363", "19384", "19371", "19359"]) and
{WORKORDER.ACTFINISH} in DateTime (2005, 06, 30,23, 59, 59) to DateTime (2006, 01, 01,00, 00, 00)

the problem is that if my eqnum has no value is is left out of the selection. I need these records to show.

Thank you in advance!

 

[lbass]

Go to report->selection formula->record and change the formula to:

(
isnull({WORKORDER.ACTFINISH}) or
{WORKORDER.ACTFINISH} in DateTime (2005, 06, 30,23, 59, 59) to DateTime (2006, 01, 01,00, 00, 00)
) and
not ({WORKORDER.EQNUM} in ["19375", "19376", "19382", "19378", "19381", "19373", "19370", "19368", "19363", "19384", "19371", "19359"])

-LB

 

[synapsevampire]

Rather than the select expert, use Report->Edit Selection Formula->Record and adjust it to:

(
isnull({WORKORDER.EQNUM})
or
trim({WORKORDER.EQNUM}) = ""
)
and
(
not ({WORKORDER.EQNUM} in ["19375", "19376", "19382", "19378", "19381", "19373", "19370", "19368", "19363", "19384", "19371", "19359"])
)
and
(
{WORKORDER.ACTFINISH} in DateTime (2005, 06, 30,23, 59, 59) to DateTime (2006, 01, 01,00, 00, 00)
)

There was also a typo in your formula in that the starting bracket for the IN wasn't showing for the EQNUMs.

I've made the assumption that you still want the date portion of the filtering to apply to those with null EQNUMs.

If you'd like to supply ample information with your posts, include:

Crystal version
Database/connectivity used
Example data (not what's in the report, the data!)
Expected output (what should the report show based no the example data)

-k

 

[lbass]

Sorry, I misread and thought it was the date field that could be null.

-LB

 

[dnboughton]

thanks to you both! I got it.