Author |
Message |
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Apr 2012 at 7:44am |
{table.expirationdate} in currentdate to dateadd('d',90,currentdate)
|
IP Logged |
|
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
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 Logged |
|
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Apr 2012 at 9:06am |
try this:
date(picture( {MasterPolicy.ExpDate},'xx/xx/xxxx'))
in currentdate to dateadd('d',90,currentdate)
|
IP Logged |
|
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
Posted: 17 Apr 2012 at 10:22am |
Worked perfectly, although I don't understand the "(picture({Masterpolicy}" part. What is "picture"?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
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 Logged |
|
|