INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Null/Blank Field producing inaccurate data

Null/Blank Field producing inaccurate data

(OP)
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)

RE: Null/Blank Field producing inaccurate data

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
www.decisionfirst.com

RE: Null/Blank Field producing inaccurate data

(OP)
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.

RE: Null/Blank Field producing inaccurate data

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
www.decisionfirst.com

RE: Null/Blank Field producing inaccurate data

(OP)
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))


RE: Null/Blank Field producing inaccurate data

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
www.decisionfirst.com

RE: Null/Blank Field producing inaccurate data

(OP)
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.

RE: Null/Blank Field producing inaccurate data

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close