| Author |
Message |
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
   Topic: Can't get simple numeric date to convert to actual
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!!!
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Feb 2012 at 12:24pm |
one possible solution:
datediff('d',currentdate,date(picture({table.field},"xx/xx/xxxx"))) in 0 to 90
|
|
IP Logged |
|
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
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/
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
|
|
IP Logged |
|
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
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?
|
|
IP Logged |
|
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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}
Edited by DBlank - 07 Feb 2012 at 5:33am
|
|
IP Logged |
|
jbattelle
Newbie
Joined: 06 Feb 2012
Location: United States
Online Status: Offline
Posts: 25
|
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?
|
|
IP Logged |
|
|
|
