Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Null values Post Reply Post New Topic
Page  of 3 Next >>
Author Message
zanoknee
Newbie
Newbie


Joined: 10 Jan 2012
Online Status: Offline
Posts: 14
Quote zanoknee Replybullet Topic: Null values
    Posted: 10 Jan 2012 at 7:46am
The report searching for DOCTOR.OTHERNO that is null works if I search dates before 2008 but does not work for dates in 2011 and 2012. My formula is:

isNull ({DOCTOR.OTHERNO})or
{DOCTOR.OTHERNO} like "N*" and
{@Fill Date} = {?Date Range} and
{@Qty} > 0.00 and
{TRANS.DEPT} = {?Select Pharmacy}

I have tried putting this in as a select expert as well as a selection formula however it pulls the incorrect dates if the date field is after 2008.

Any help would be greatly appreciated.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Jan 2012 at 7:57am
not exactly sure what you want to search to be but likely you want parenth around your or portion
 
(isNull ({DOCTOR.OTHERNO})
or
{DOCTOR.OTHERNO} like "N*")
and
{@Fill Date} = {?Date Range} and
{@Qty} > 0.00 and
{TRANS.DEPT} = {?Select Pharmacy}


Edited by DBlank - 10 Jan 2012 at 7:58am
IP IP Logged
zanoknee
Newbie
Newbie


Joined: 10 Jan 2012
Online Status: Offline
Posts: 14
Quote zanoknee Replybullet Posted: 10 Jan 2012 at 8:11am
When I put the parenthesis in it returns no results and wipes my data. I am trying to search for doctors who have a null other number or an other number that starts with N
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Jan 2012 at 8:13am
can you explain how you want the filter to work?
IP IP Logged
zanoknee
Newbie
Newbie


Joined: 10 Jan 2012
Online Status: Offline
Posts: 14
Quote zanoknee Replybullet Posted: 10 Jan 2012 at 8:15am
I want the Doctor other number to show if it is null or starts with N for a specified filled on date range for a specific department. I want the medication qty to be greater than 0
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Jan 2012 at 8:28am
for debugging you can remove each condition and rerun the report to see which is the one not acting as expected.
My guess is the date is the problem based on the way it is labaled. It is odd to see a date "range" set to use an = condition.
 
I think your original criteria would have returned all of the NULL doc rows or any rows where ALL of the AND conditions were met.
 
isNull ({DOCTOR.OTHERNO}) or
{DOCTOR.OTHERNO} like "N*" and
{@Fill Date} = {?Date Range} and
{@Qty} > 0.00 and
{TRANS.DEPT} = {?Select Pharmacy}
 
IP IP Logged
zanoknee
Newbie
Newbie


Joined: 10 Jan 2012
Online Status: Offline
Posts: 14
Quote zanoknee Replybullet Posted: 10 Jan 2012 at 8:33am
I agree I have never written a date range with an = before a co worker started this report and gave it to me to finish. I normally write mine off of parameters. Do you have any suggestions on how to change the formula to go off of a begin date and end date parameter?


Edited by zanoknee - 10 Jan 2012 at 8:33am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Jan 2012 at 8:38am
You will have to look at the ?Date Range param and see how it is set up in the report.
my guess is that your coworker set up a date parameter that has 'allow range values' set to TRUE.
if that is the case try this...
 
(isNull ({DOCTOR.OTHERNO})
or
{DOCTOR.OTHERNO} like "N*")
and
{@Fill Date} in minimum({?Date Range}) to Maximum({?Date Range})
 and
{@Qty} > 0.00 and
{TRANS.DEPT} = {?Select Pharmacy}
IP IP Logged
zanoknee
Newbie
Newbie


Joined: 10 Jan 2012
Online Status: Offline
Posts: 14
Quote zanoknee Replybullet Posted: 10 Jan 2012 at 8:44am
Still no data :(
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Jan 2012 at 8:53am

I recommend debugging it by alterning your statement to run each portion by itself and see if each by itself doe what you expect/want. Run each color line and see which item or items are causing you issue. Then you can spen time on the correct "problem" rather than assuming you understand the problem and that assumption being wrong.

 
(isNull ({DOCTOR.OTHERNO})
or
{DOCTOR.OTHERNO} like "N*")
//and
{@Fill Date} in minimum({?Date Range}) to Maximum({?Date Range})
 //and
{@Qty} > 0.00
//and
{TRANS.DEPT} = {?Select Pharmacy}
Which one(s) did not give you the correct result for that condition?


Edited by DBlank - 10 Jan 2012 at 8:53am
IP IP Logged
Page  of 3 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.