Print Page | Close Window

Date Range question

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=16204
Printed Date: 28 Apr 2024 at 9:50pm


Topic: Date Range question
Posted By: jbattelle
Subject: Date Range question
Date 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!!


Replies:
Posted By: DBlank
Date Posted: 17 Apr 2012 at 7:44am
{table.expirationdate} in currentdate to dateadd('d',90,currentdate)

Posted By: jbattelle
Date 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,

Posted By: jbattelle
Date 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?

Posted By: DBlank
Date 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)

Posted By: jbattelle
Date 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".

Posted By: DBlank
Date Posted: 17 Apr 2012 at 9:06am
try this:
date(picture({MasterPolicy.ExpDate},'xx/xx/xxxx'))
 in currentdate to dateadd('d',90,currentdate)

Posted By: jbattelle
Date Posted: 17 Apr 2012 at 10:22am

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

Posted By: DBlank
Date 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?

Posted By: jbattelle
Date 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.


Print Page | Close Window