Checking for Null Date fields 1

[FunkyBunch]

I am using CR9(SQL 2005) and am having trouble making the inspections with null dates appear. Here is my selection code this far:

IF ({@UnitIsBlank} and {@DivisionIsBlank}) and ({Command.NEXT_INSP_DATE_FW} <= {?ReportPeriodEnd} or isnull({Command.NEXT_INSP_DATE_FW})
or {@Due Date} = cdate(null))THEN True
//****End of statement

//All of these are just every possible combination of valid parameter entries, with the
//result being the record selection that will be applied for that scenario.
Else

IF Not {@UnitIsBlank} and {@DivisionIsBlank} and ({Command.NEXT_INSP_DATE_FW} <= {?ReportPeriodEnd}) or isnull({Command.NEXT_INSP_DATE_FW}) then
{Command.UNIT_FW} = {?ReportVehicle}

else

IF {@UnitIsBlank} and Not {@DivisionIsBlank} and ({Command.NEXT_INSP_DATE_FW} <= {?ReportPeriodEnd}) or isnull({Command.NEXT_INSP_DATE_FW})then
{Command.depot_name_Fw} = {?ReportCostCentre}

else

IF not {@UnitIsBlank} and not {@DivisionIsBlank} and ({Command.NEXT_INSP_DATE_FW} <= {?ReportPeriodEnd}) or isnull({Command.NEXT_INSP_DATE_FW}) then

{Command.depot_name_Fw} = {?ReportCostCentre} and
{Command.UNIT_FW} = {?ReportVehicle}

I know that I am only checking for null in the first field but that is just to for testing purposes. the code for the "isblank" fields is <variable> = "".

 

[lbass]

1) You must test for nulls before referencing the field that can be null.
2) You must set off your "or" clause and the clauses that represent its alternatives with parens; otherwise, the report will not know what you intend.
3) You must share the actual content of formulas and any nested formulas in any post, as the contents can affect what solutions apply.

-LB

 

[FunkyBunch]

Here is my adjusted code:
IF ({@UnitIsBlank} and {@DivisionIsBlank}) and (({Command.NEXT_INSP_DATE_FW} <= {?ReportPeriodEnd}) or isnull({Command.NEXT_INSP_DATE_FW}))THEN True
//****End of statement

//All of these are just every possible combination of valid parameter entries, with the
//result being the record selection that will be applied for that scenario.
Else

IF Not {@UnitIsBlank} and {@DivisionIsBlank} and ({Command.NEXT_INSP_DATE_FW} <= {?ReportPeriodEnd}) or isnull({Command.NEXT_INSP_DATE_FW}) then
{Command.UNIT_FW} = {?ReportVehicle}

else

IF {@UnitIsBlank} and Not {@DivisionIsBlank} and ({Command.NEXT_INSP_DATE_FW} <= {?ReportPeriodEnd}) or isnull({Command.NEXT_INSP_DATE_FW})then
{Command.depot_name_Fw} = {?ReportCostCentre}

else

IF not {@UnitIsBlank} and not {@DivisionIsBlank} and ({Command.NEXT_INSP_DATE_FW} <= {?ReportPeriodEnd}) or isnull({Command.NEXT_INSP_DATE_FW}) then

{Command.depot_name_Fw} = {?ReportCostCentre} and
{Command.UNIT_FW} = {?ReportVehicle}

The ?ReportCostCentre is a free-text parameters as well as the ?ReportVehicle variable. As described in the first post the .....IsBlank variables are: (?ReportCostCentre) = "" and the same with (?ReportVehicle) = ""

Thanks for all of your help.

 

[synapsevampire]

Try this sort of structure instead, checking for nulls first in each section:

(
IF isnull({Command.NEXT_INSP_DATE_FW})
or
(
Not({@UnitIsBlank} and {@DivisionIsBlank})
and
{Command.NEXT_INSP_DATE_FW} <= {?ReportPeriodEnd}
) then
{Command.UNIT_FW} = {?ReportVehicle}
)

etc...

Including more parentheticals will improve readability and help Crystal parse your criteria.

I generally won't respond to posts that have formulas that contain other formulas that aren't included, but this seems fairly straightforward.

-k

 

[FunkyBunch]

Thanks a ton, it worked!!!

For next time, do you want me to go as far as including the sql command driving the report?

thanks again synapsevampire!

 

[synapsevampire]

Well the SQL Command can be useful if we intend to replace it, or you fear that you're getting poor performance.

My issue was when you reference an {@formula} as we don't know what's in that formula.

Glad that you got it working, Crystal is curiously persnickety with this sort of thing.

-k