Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Date Range question Post Reply Post New Topic
Author Message
jbattelle
Newbie
Newbie


Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
Quote jbattelle Replybullet Topic: Date Range question
    Posted: 17 Apr 2012 at 7:20am
I am using Crystal XI and am fairly new. I work for an insurance company and we have policies that expire, and I need to pull a report of all policies that will expire within the next 90 days. None of the out of the box date range formulas will work, so I need a little help while I am improving my formula skills.
 
Any help would be great.

Thanks!!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 17 Apr 2012 at 7:44am
{table.expirationdate} in currentdate to dateadd('d',90,currentdate)
IP IP Logged
jbattelle
Newbie
Newbie


Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
Quote jbattelle Replybullet Posted: 17 Apr 2012 at 7:50am
OK that errored out. I replaced the field name with my field name and pasted that in the record selection. ANy other ideas?

Thanks,
IP IP Logged
jbattelle
Newbie
Newbie


Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
Quote jbattelle Replybullet Posted: 17 Apr 2012 at 7:53am
Let me clarify that I have converted my expiration date field into a date field by the below formula:
 
local stringvar datestr := totext({MasterPolicy.ExpDate});
left(datestr,2)+"/"+mid(datestr,3,2)+"/"+right(datestr,4)
 
Does that make a difference?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 17 Apr 2012 at 7:57am
why did you convert it to text?
 
this should go in the select expert
{MasterPolicy.ExpDate} in currentdate to dateadd('d',90,currentdate)
IP IP Logged
jbattelle
Newbie
Newbie


Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
Quote jbattelle Replybullet Posted: 17 Apr 2012 at 8:55am
Because my data comes over like this: 01311900. When I tried it without the conversion, it says "a date time is required here".
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 17 Apr 2012 at 9:06am
try this:
date(picture({MasterPolicy.ExpDate},'xx/xx/xxxx'))
 in currentdate to dateadd('d',90,currentdate)
IP IP Logged
jbattelle
Newbie
Newbie


Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
Quote jbattelle Replybullet Posted: 17 Apr 2012 at 10:22am

Worked perfectly, although I don't understand the "(picture({Masterpolicy}" part. What is "picture"?

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 17 Apr 2012 at 10:33am

your original data type was coming in as a string (e.g. "01311900").

in order to use a date range for the data select  (in currentdate to dateadd('d',90,currentdate) )
you needed to convert it to a date ( date(field))
however your string format was missing the "/" to make it a valid date format so the field needed to be converted to a valid format.
picture(x,y) prints a string in a particluar format. X is the input string and Y is the 'applied picture'.
picture({MasterPolicy.ExpDate},'xx/xx/xxxx') converted the 01311900 into 01/31/1900 which is a valid date format. This then let the date() function work because the string it was using was the picture version of your data field.
Does that help?
IP IP Logged
jbattelle
Newbie
Newbie


Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
Quote jbattelle Replybullet Posted: 17 Apr 2012 at 11:26am
Yep that makes sense. Good trick. The DB my company uses is so screwy, I have to manipulate stuff constantly. Thanks again for your help.
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.