Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Formula giving Incorrect Results Post Reply Post New Topic
Author Message
Bassman75
Newbie
Newbie
Avatar

Joined: 23 Mar 2017
Location: United States
Online Status: Offline
Posts: 4
Quote Bassman75 Replybullet Topic: Formula giving Incorrect Results
    Posted: 29 Mar 2017 at 7:03am
I'm using Crystal 2011 and have what I thought was a pretty cut and dry formula, however if I use a date range in 2017, the results are correct. When I use any dates in 2016, I get the result of the "else" in the statement for all records, when I should be getting ".84" for a majority since the Date Parameter is less than the hard coded date.

Scan Date Formula:

Local Datetimevar scanDATE:={posj_event.business_date};
Local Stringvar scanDateSTR:=ToText(scanDATE,"MMddyy");
scanDateSTR

Formula that does not work when 2016 date is entered:

Local NumberVar rateNUM:=If upcSTR="3943000050" and {@Scan Date} < "030617"
then rateNUM:=.84 else rateNUM:=.01;
rateNUM
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 29 Mar 2017 at 10:57am
Have you double checked the value of scanDateSTR to see if you are getting the value you are expecting? You also do not show where the value for upcSTR comes from.  We can only assume that is correct also.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Mar 2017 at 10:59am
Also, I would really not use a < comparison on a string like that. Use the date data types, not strings.
A quick test in crystal returns a false on
"120616" < "030617".
IP IP Logged
Bassman75
Newbie
Newbie
Avatar

Joined: 23 Mar 2017
Location: United States
Online Status: Offline
Posts: 4
Quote Bassman75 Replybullet Posted: 30 Mar 2017 at 2:11am
I am getting the value I am expecting,

120216
120316
120416
120516

IP IP Logged
Bassman75
Newbie
Newbie
Avatar

Joined: 23 Mar 2017
Location: United States
Online Status: Offline
Posts: 4
Quote Bassman75 Replybullet Posted: 30 Mar 2017 at 2:13am
DBlank, I will give that a try. So what you are saying is use the actual value of the date, which is originally a date data type, rather than convert it to a string?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Mar 2017 at 2:35am
correct
IP IP Logged
Bassman75
Newbie
Newbie
Avatar

Joined: 23 Mar 2017
Location: United States
Online Status: Offline
Posts: 4
Quote Bassman75 Replybullet Posted: 30 Mar 2017 at 2:41am
Got it, works like a charm. Interesting that converting to a string behaves that way. Thank you for the help!!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Mar 2017 at 2:46am
"120616" < "030617"
it isn't numeric
the 1 is > the 0
I think, if you had to use as string you want to use
yyyyMMdd format but I still think using the actual date field is way more reliable.
IP IP Logged
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.031 seconds.