Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: currentdate and selection criteria Post Reply Post New Topic
Author Message
PBSELDEN
Newbie
Newbie
Avatar

Joined: 18 Jun 2019
Online Status: Offline
Posts: 10
Quote PBSELDEN Replybullet Topic: currentdate and selection criteria
    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.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet 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)
IP IP Logged
PBSELDEN
Newbie
Newbie
Avatar

Joined: 18 Jun 2019
Online Status: Offline
Posts: 10
Quote PBSELDEN Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)))


Edited by DBlank - 01 Jul 2019 at 3:27am
IP IP Logged
PBSELDEN
Newbie
Newbie
Avatar

Joined: 18 Jun 2019
Online Status: Offline
Posts: 10
Quote PBSELDEN Replybullet 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.
IP IP Logged
PBSELDEN
Newbie
Newbie
Avatar

Joined: 18 Jun 2019
Online Status: Offline
Posts: 10
Quote PBSELDEN Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.