Null/Blank Field producing inaccurate data

[CrystalBox]

I'm using Crystal Report 11. I've run into a bump in the road with the record selection formula shown below. The report displays cases that are "pending", which means that the cases have not been classified with a classification code or classification date. Once in awhile a case that has been given a "classification" and "classification date will show on my "pending" report. I discovered that sometimes users will enter the "classification" code and "classification date" and hit the return key a couple of times, creating a blank space in those fields. The record selection formula will pickup the blank space and place the record in my "pending" report. What can I add to this formula to prevent it from happening? Thank you

(isNull({EXP_CLASSIFICATION.CLASSIFICATION}))
OR

(Trim({EXP_CLASSIFICATION.CLASSIFICATION})= "")
AND
Trim({EXP_CLASSIFICATION.CHANGE})=""

AND


IsNull({EXP_CLASSIFICATION.DATE})
OR {EXP_CLASSIFICATION.DATE} = date(0,0,0)

 

[hilfy]

It's an issue with parentheses in the "OR" parts of your criteria. Try this:

((isNull({EXP_CLASSIFICATION.CLASSIFICATION}))
OR (Trim({EXP_CLASSIFICATION.CLASSIFICATION})= "") )

AND Trim({EXP_CLASSIFICATION.CHANGE})=""

AND ( IsNull({EXP_CLASSIFICATION.DATE})
OR {EXP_CLASSIFICATION.DATE} = date(0,0,0))

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[CrystalBox]

Thanks Dell. Sorry for the late reply. I didn't think I was going to get a response. I tried your suggestion but it returned zero data on my report. I not quite sure what's going on.

 

[hilfy]

So, if I understand you correctly, you do NOT want the nulls or blank spaces to appear on the report. If that's correct, then this formula is kind of backwards - it will pull everything that is null or blank.

If this is the case, Try this:

not isNull({EXP_CLASSIFICATION.CLASSIFICATION})) and
Trim({EXP_CLASSIFICATION.CLASSIFICATION}) <> "" and
Trim({EXP_CLASSIFICATION.CHANGE}) <> "" and
not IsNull({EXP_CLASSIFICATION.DATE}) and
{EXP_CLASSIFICATION.DATE} > date(0,0,0))

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[CrystalBox]

I inserted the formula you suggested but it's telling me "the remaining text does not appear to be part of the formula" (I put the problem text in bold for your reference).
The problem that I have is when a user inputs data in a field but sometimes inadvertently will hit the return and create space (poorly designed database). The formula ignores the data because of the space created by the user and the record gets put on the report as uncompleted.


not isNull({EXP_CLASSIFICATION.CLASSIFICATION})) and
Trim({EXP_CLASSIFICATION.CLASSIFICATION}) <> "" and
Trim({EXP_CLASSIFICATION.CHANGE}) <> "" and
not IsNull({EXP_CLASSIFICATION.DATE}) and
{EXP_CLASSIFICATION.DATE} > date(0,0,0))

 

[hilfy]

There's an extra close parenthesis at the beginning of what's in bold. Remove it.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[CrystalBox]

Thanks again Dell. Your formula didn't give me the desired results. My original formula works fine with one exception.

There are the three fields that I reference in the formula

Classification Classification Change Classification Date

The purpose of the report is to provide me with those records that have not been given a classification. Once in a while a user will create additional space after the three fields (below) have been filled in with data.

Classification Classification Change Classification Date
A No change 4/21/15
space space space

My formula will ignore the data that has been inserted and act on the space created inadvertently by the user. Ideally, it would be nice if I could fix it from the database but I have no control over the design of the database. Sorry if I'm not being clear.

 

[CrystalBox]

Dell, thanks for your help. I just have to figure out how to get the formula to stop immediately when data is encountered, this way if there are null fields it will prevent my report from displaying inaccurate information.