Print Page | Close Window

Can't get simple numeric date to convert to actual

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=15556
Printed Date: 06 May 2024 at 4:03am


Topic: Can't get simple numeric date to convert to actual
Posted By: jbattelle
Subject: Can't get simple numeric date to convert to actual
Date Posted: 06 Feb 2012 at 12:04pm
I am using Crystal XI and am fairly new to Crystal. I am trying to create a report that only shows me data from today's date and records 90 days forward. the problem is is that my date field data comes in as: 01012012 as an example, however I need it to be an actual date field like: 01/01/2012 so that I can then filter the data by a selected date range. Please help and let me know if you have any frther questions I have not clarified.
 
THANKS FOR YOUR HELP IN ADVANCE!!!


Replies:
Posted By: DBlank
Date Posted: 06 Feb 2012 at 12:24pm

one possible solution:

datediff('d',currentdate,date(picture({table.field},"xx/xx/xxxx"))) in 0 to 90

Posted By: jbattelle
Date Posted: 07 Feb 2012 at 5:00am
Thanks for your help. I placed this in my report and when I run the report, it returns "TRUE" or "false". It is no longer a date field altogether. Any other ideas/

Posted By: DBlank
Date Posted: 07 Feb 2012 at 5:12am
the code I gave you was to be used in the select expert (not as a display formula). It includes the TRUE rows and excludes the FALSE rows.
 
date(picture({table.field},"xx/xx/xxxx"))
will convert the string toa  date type

Posted By: jbattelle
Date Posted: 07 Feb 2012 at 5:12am
Let me be a little more clear. Although, I think your formula is showing me that the date field is "true" aka within 90 days from today's date, how do I run the report to ONLy list records that meet this criteria?

Posted By: jbattelle
Date Posted: 07 Feb 2012 at 5:13am
OK Dblank, gotcha. Let me try it and I'll let you know.
 
Thanks again for all your help.

Posted By: DBlank
Date Posted: 07 Feb 2012 at 5:16am

as a note,

the select expert is used to write a boolean formula that evaluates each row. If the formual returns true the row is included, if it returns false it is ecxcluded.

Posted By: jbattelle
Date Posted: 07 Feb 2012 at 5:26am
Worked Perfectly!!! Thanks again. One caviat here. The report is showing duplicates based on some other criteria in the DB. Is there a way to only show a particular record once. In other words, hide duplicates. I promise, this is my last question. lol

Posted By: DBlank
Date Posted: 07 Feb 2012 at 5:32am

usually yes you can, but note that suppressing rows do not exclude them so they are still used in any calculations (counts, sums, averages, etc.) unless you explicitly exclude them via shared variable formulas or Running Totals.

If you have a primarykey that can be used to find duplicates
go into the section expert,
select details section
formula editor next to ssuppress (no drill down) and use
previous({table.PK})={table.PK}

Posted By: jbattelle
Date Posted: 07 Feb 2012 at 5:50am

Thanks again. You are a genius. Hopefully I will get better at these, but I'm pretty green to Crystal. ANy suggestions for training or book to read up?


Posted By: DBlank
Date Posted: 07 Feb 2012 at 6:03am
Brian's book on this site is very good. I have it and reference it when I get stuck or need a refresher. I also think Crystal Help files are good (once you get your bearings in the software).
I also recommend reading as many posts from this site as you can. Try to replicate the issue and solution in a sandbox report. I personally prefer that kind of hands on testing as it makes me think through what is happening. You also will get multiple approaches for solutions from different posters as thare are often different ways to address one issue.

Posted By: jbattelle
Date Posted: 07 Feb 2012 at 6:06am
Will do. Thanks again for your help on this one! Cheers!


Print Page | Close Window