Print Page | Close Window

currentdate and selection criteria

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=22791
Printed Date: 28 Apr 2024 at 11:48am


Topic: currentdate and selection criteria
Posted By: PBSELDEN
Subject: currentdate and selection criteria
Date Posted: 27 Jun 2019 at 12:19pm
I want to create a report that selects data with a simple date range. Ideally I would do something like set the "{event.date} in (currentdate-15) to (currentdate-1)".
That way, every time the report runs, it simply reports out the last 2 weeks of activity without me having to reset the dates. But Crystal doesn't seem to like currentdate as a selection criterion. It is looking for a string because that is what {event.date} is. I've tried things with DateValue, and MonthToDate and cdate and so forth, but haven't found the right path.
I suspect that all of these are different dates, number, strings--whatever. The dates in the {event.date} table look like 20190626. CurrentDate looks like 6/20/2019. I don't know about MonthToDate which would be the easiest to use of all, but when I try to use it, I get an error code saying that it is looking for a string.
I am open to all good ideas.


Replies:
Posted By: kevlray
Date Posted: 27 Jun 2019 at 1:04pm
In the record selection formula, can you do something like this:
cdate({event.date}) in (currentdate-15) to (currentdate-1)

Posted By: PBSELDEN
Date Posted: 28 Jun 2019 at 11:03am
wonderful idea, but I get a "bad date format string" message. I'm not certain that I know what date format they want me to be in? or how I get them all in the proper format.

Posted By: DBlank
Date Posted: 01 Jul 2019 at 3:27am
You have to convert your event string into something that can be used as a valid date, something like:

date(tonumber(left({event.date},4)),tonumber(mid({event.date},5,2)),tonumber(mid({event.date},7,2)))

Posted By: PBSELDEN
Date Posted: 01 Jul 2019 at 10:46am
they don't make it easy do they. I'll give this a try. I've already done something like this, but I didn't use the "tonumber" together with the reformating of the dates.

Posted By: PBSELDEN
Date Posted: 02 Jul 2019 at 7:53am
THANKS so much. This did the trick.
first I converted the date to a date string by creating a formula ({eventdate}) that used date(tonumber({...
Then I ended up using the new {eventdate} with monthtodate.


Print Page | Close Window