date range display question

Hi trs28,

I don't know whether it is possible to do that, but just a caution on using date ranges in this way.
If your database fields are datetime fields rather than date fields CR is basing its include/exclude rules on the exact second.
(So don't assume it is working in whole days).

Pete

wouldn't the range start at the very beginning of the start date & the very end of the end date?    It's important for the data to be in date/time format, but for reporting purposes, time is insignificant.

Look at the values in the parameter prompt dialog.
If the end datetime is 01-jun-2006 00:00:00 then that is quite precise.

Pete

for anyone that looks at this ..... i figured it out ..... (too bad you can't give points to yourself!  lol)  ....by the way, this is in a formula, for displaying the selected range on the report, that i have partially done so far - the code below is how i trapped whether or not a start date was entered.  i'm sure the other half of the date range is trapped in a similar manner.

if (year(minimum({?DateRange})) = 0)
    then
       ('EVERYDAY' + ' - ' + totext(maximum({?DateRange}),"MM/dd/yyyy"))
    else
        (totext(minimum({?DateRange}),"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange}),"MM/dd/yyyy"));



anyone know how to give points to yourself?   HA!   I jacked the points up to 500 for anyone that can answer that one!

oops ... i didn't trap the "include" value ... i trapped the "no lower bounds" value .... kindly disregard that last posting :)

Do you still require help?

mlmcc

Good thinking Batman!

Pete

that was very good thinking indeed!  James, you're definitely going to be getting a bunch of points for that one, but this now brings me to another dilemma ....  oh and pete, i'm planning on giving you points too buddy  ;-)

i want to adjust the diplay date to account for that.  in other words, if you select 01/01/2006 (not inclusive) as the max value, I want to be able to diplay the end value as 12/31/2005, but I'd like to be able to do it EASILY.  :)  

I was just going to create variables for the 3 numbers, parse out the values, subtract day value by 1, and then piece the 3 together for output, but then i realized how many conditions i would need to account for different lengths of the various months, etc.    

There wouldn't happen to be a PreviousDay or PriorDay function or something along those lines would there !?!?!?!  

...oh as far as the "giving points to yourself" thing goes, that was just a little late night sarcasm/humor.   :)

- travis

I figured it out ... for anyone that's interested, here's the code that traps both the "include this value" and "no lower/upper bounds" options for the start and end dates .....



local booleanVar minInc := (minimum({?DateRange}) in {?DateRange});
local booleanVar maxInc := (maximum({?DateRange}) in {?DateRange});

if (year(minimum({?DateRange})) = 0) then   //....if no lower bound date
(
    if (maxInc) then   //....if end date falls within actual queried date range
    (
        totext(minimum({AchReport.AchDate}),"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange}),"MM/dd/yyyy")
    )                                                               //...prints lowest order date as start date (end inclusive)
    else
    (
        totext(minimum({AchReport.AchDate}),"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange})-1,"MM/dd/yyyy")
    )                                                               //...prints lower order date as start date (end NOT inclusive)
)
else
(    
    if (year(maximum({?DateRange})) = 0) then        //....if no upper bound date
    (
        if (minInc) then    //....if start date falls within actual queried date range
        (
            totext(minimum({?DateRange}),"MM/dd/yyyy") + ' - ' + totext(maximum({AchReport.AchDate}),"MM/dd/yyyy")
        )                                                        //...prints most recent order date as end date (start inclusive)
        else
        (
            totext(minimum({?DateRange})+1,"MM/dd/yyyy") + ' - ' + totext(maximum({AchReport.AchDate}),"MM/dd/yyyy")
        )                                                       //....prints most recent order date as end date (start NOT inclusive)  
    )
    else
    (
        if (minInc and maxInc) then
        (
            totext(minimum({?DateRange}),"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange}),"MM/dd/yyyy")
        )                                                       //...prints user selected range as is (both inclusive)
        else
        (
            if (minInc and not maxInc) then
            (
                totext(minimum({?DateRange}),"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange})-1,"MM/dd/yyyy")
            )                                                   //...end date not inclusive
            else
            (
                if (not minInc and maxInc) then
                (
                    totext(minimum({?DateRange})+1,"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange}),"MM/dd/yyyy")
                )                                               //...start date not inclusive
                else
                (
                    totext(minimum({?DateRange})+1,"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange})-1,"MM/dd/yyyy")
                )                                               //...neither dates inclusive
            )
        )
    )
);

Heh heh.  Thanks Robin.  :-)  I went through this same thing a while back, trying to figure out how to tell whether "include value" was unchecked, so I could show that on a report.  I did some research and couldn't find anything about it.  Not even a suggestion that it was possible.  It was looking like this was going to be one of those "you can't get there from here" kind of things, but then a day or two later it suddenly occurred to me to try that and it worked.


 Travis,

 Glad I could help.
 As you've figured out, you can just add or subtract a day to get the date after or before the minimum or maximum date.

 However, I think there's a problem with the formula you posted.  As I read it, you're checking for:

if (year(minimum({?DateRange})) = 0) then   //....if no lower bound date
 ...
else
  if (year(maximum({?DateRange})) = 0) then        //....if no upper bound date


 So, if both "include value" boxes are unchecked, your formula would see that the minimum was excluded and go through the first block of code and never get to the maximum check in the "else".

 Also, while it may not be a "problem", per se, your formula is somewhat redundant in that it repeatedly checks the same conditions.  You can simplify it greatly using variables.  The following is untested, but I think it should work (it may need some minor tweaking):

Local DateVar mindate;
Local DateVar maxdate;

if year (minimum ({?DateRange})) = 0 then         //....if no lower bound date
  mindate := minimum ({AchReport.AchDate})
else
  if minimum ({?DateRange}) in {?DateRange} then  //....if start date falls within date range
    mindate := minimum ({?DateRange})
  else                                            //...start date not inclusive
    mindate := minimum ({?DateRange}) + 1;

if year (maximum ({?DateRange})) = 0 then         //....if no upper bound date
 maxdate := maximum ({AchReport.AchDate})
else
  if maximum ({?DateRange}) in {?DateRange} then  //....if end date falls within date range
    maxdate := maximum ({?DateRange})
  else                                            //...end date not inclusive
    maxdate := maximum ({?DateRange}) - 1;

totext (mindate,"MM/dd/yyyy") + ' - ' + totext (maxdate,"MM/dd/yyyy")


 James

You definitely are correct about my code being redundant!   Initially I was going to somehow "mark" one of the dates if inclusive rather than adjusting the date itself, but then I got to thinking about it and realized that'd be more trouble than it's worth in the long run and frankly just bad practice.   ....so I started testing out some logic and well, what i had above worked!  

After reading your comment though, I started to wonder whether or not I did have that possible flaw that you pointed out.  The first thing it does is test for the lower bound date .... if it isn't, it tests to see if the end date is inclusive via the maxInc boolean variable ...doing it this way is how this little snippet of code became redundant, since i had to do that for all situations ....  so i think my code is okay.

Regardless, I'm a much bigger fan of your logic ... I had intentions on tweaking my horrible "if then" nightmare, but whether I would actually get the time to go over it again is another story .... so I do really appreciate your suggestion, especially because it's probably going to take the place of my original code!  :)    

You know, I was actually very surprised to see that more people haven't come across this same problem!   I was even more surprised to see how almost no one knew how to handle it !!!

Thanks again!

- Travis

There was a tiny bit of tweaking that needed done James on two lines, but I'll be honest with you, I'm a little confused as to exactly "why" it needed the adjustments ... here is the modified (fully tested and working) variation of your code - the adjustments were done in the line after the start of your two "if" blocks - for some reason, I had to encapsulate the variable assignment with date(...), but if i did that for the other assignments, crystal would bitch and say it wants a DateTime field regardless of them being identical assignments  !!?!??!    The only thing I can think of is that Crystal must only be picky about the assignment when a value is first assigned & then just disregard the time if it needs to be reassigned.   Other than that, it works great buddy!   :-D


Local DateVar mindate;
Local DateVar maxdate;

if year (minimum ({?DateRange})) = 0 then         //....if no lower bound date
     mindate := date(minimum ({AchReport.AchDate}))
else
  if minimum ({?DateRange}) in {?DateRange} then  //....if start date falls within date range
    mindate := minimum ({?DateRange})
  else                                            //...start date not inclusive
    mindate := minimum ({?DateRange}) + 1;

if year (maximum ({?DateRange})) = 0 then         //....if no upper bound date
 maxdate := date(maximum ({AchReport.AchDate}))
else
  if maximum ({?DateRange}) in {?DateRange} then  //....if end date falls within date range
    maxdate := maximum ({?DateRange})
  else                                            //...end date not inclusive
    maxdate := maximum ({?DateRange}) - 1;

totext (mindate,"MM/dd/yyyy") + ' - ' + totext (maxdate,"MM/dd/yyyy")

You're welcome again.  :-)

 It still seems to me that when the minimum "include value" box was unchecked, your original code would have not checked if the maximum box was checked, but I guess it's a fairly moot point if you're not using that code anymore.

 As for having to add Date(), I have a couple questions.

 Is your ?DateRange parameter type Date or DateTime?

 Is the AchReport.AchDate field type Date or DateTime?

 If the parameter is Date and the field is DateTime, that would explain it.

 James

Oh wow!  I must be slipping b/c for some reason I was thinking that each of the 3 assignment were to the same thing -> maximum({?DateRange}) when I was writing that up!   You were right though, the AchReport.AchDate field is a DateTime and I created the parameter as a Date parameter.  

 //***The first thing looked at is whether the start date is unbound.
if (year(minimum({?DateRange})) = 0) then

     //***if it isn't, then it checks to see if the end date is unbound.
     if (year(maximum({?DateRange})) = 0) then

          //***if neither are unbound, it first checks to see if BOTH include values are checked
          if (minInc and maxInc) then

          //***if minimum is checked and max is unchecked
          if (minInc and not maxInc) then
       
          //***if maximum is checked and minimum is unchecked
          if (not minInc and maxInc) then

keep in mind, my boolean variables at the beginning of the code will tell me whether or not both are inclusive.

local booleanVar minInc := (minimum({?DateRange}) in {?DateRange});
local booleanVar maxInc := (maximum({?DateRange}) in {?DateRange});

you're right though about it being a moot point, especially since i'm using your code!   :)

Oops.  I got stuck on the "include value" boxes I guess.  The tests I was referring to were actually for the lower and upper bounds.  If the "no lower bound" and "no upper bound" boxes were both checked, the first "if" would have been true, so you wouldn't have gotten to the "else", which is where it tested to see if there was an upper bound.  Sorry for the confusion.

 James

Well James,

I must say .... I'm impressed - you got me!   My code doesn't account for that whatsoever, but tell me ... what version of crystal are you using that allows you to select both "no lower bound" and "no upper bound" ??????   ha ha ha ha ha!  :-D   looks like i may have got you on that one buddy, huh !?!   lol


- Travis

Heh heh.  You're completely right.  CR won't let you select both.  I don't use range parameters very often and had completely forgotten about that.  So your code was OK after all.  Well, that was a big waste of time.  :-)

 I don't know why CR won't let you select both.  Seems like that would be the way to select "all".  Hmm.

 James

A waste of time?   Nah.   Well, at least not for me!  :)   Besides, I enjoyed our little back and forth mini-chat.   Take care James.

- Travis

Thanks.  You too.

 James