Date Increments - URGENT!!
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8349
Printed Date: 04 Apr 2025 at 10:29am
Topic: Date Increments - URGENT!!
Posted By: asadiq
Subject: Date Increments - URGENT!!
Date Posted: 16 Nov 2009 at 5:35am
|
Hi,
I need to work out how to print and increment all date values between 2 given dates using a For loop...
for example....
Date1 - 01/10/09
Date2 - 05/10/09
I need it to print
01/10/09
|
Replies:
Posted By: lockwelle
Date Posted: 17 Nov 2009 at 6:32am
|
local stringvar allDate;
local datevar aDate := {table.startDate};
While aDate <= {table.endDate}
Do(
aDate := DATEADD("m",1,aDate);
allDate := allDate + aDate +chrw(13) + chrw(10);
);
this will increment the date, but it will only print the last case. In order to get the display, you would need to place it in a string variable, but then you will get them all in a one text box.
You might want to put them in an array at the start of the report and then use the array to group on (haven't done this, but it might be possible).
|
Posted By: asadiq
Date Posted: 17 Nov 2009 at 8:38am
|
hi,
Thanks for your response.
However; I had that part worked out already using a For loop.
I am stuck on the last part where it returns only the last date after all the increments rather than printing each date within a range on a separate line. Basically need it to compile an array of dates using result from the For loop.
I cannot put them in an array at the start because the start and the end dates are not fixed dates. They are 2 fields in the database for example Date 1 and Date 2 which would be different for every record.
Hope I am making sense.
Thanks,
|
Posted By: asadiq
Date Posted: 17 Nov 2009 at 8:51am
|
Sorry - I did not mention that I am working with Crystal 8.5
And i just tried your code to see how that works but it is not working.
Is chrw a function? Or what should that part be doing?
Thanks
|
Posted By: lockwelle
Date Posted: 17 Nov 2009 at 9:32am
|
chrw is a function...it is creating a crlf character.
since you are using an array and you know the enddate...I am thinking you know the number of iterations,
a) create the array, since you know the number of iterations.
b) aDate := DateAdd("m",i,{table.startDate});
where i is the number of the iteration. Since the array starts at 0, that could be the start date.
I would ignore the part about the updating of the allDate as it is a string. You would want a shared datetimevar arr aDate. I don't work with arrays, so my syntax might be off a bit.
HTH
|
Posted By: asadiq
Date Posted: 17 Nov 2009 at 10:13am
|
I want to use an array on returned date values but I am not sure how to create an array from loop results....that is the part I am really stuck on.
I am using iterations but I am still getting just the last date at the end of the loop....
i need it to print the result of each iteration and build an array
|
Posted By: lockwelle
Date Posted: 17 Nov 2009 at 10:31am
|
I don't know the exact syntax but if you know the number of iterations you should be able to create the array.
It appears that the syntax is similar to:
shared datetimevar arr aDate;
local numbervar iter; //set this to the number of iterations
local numbervar i;
Redim aDate[iter];
For i:=0 to iter Do
(
aDate := dateadd("m",i,{table.startDate};
);
this will create an array that has the dates...if you want to see the values, that might be problematic as the number of array values is indeterminate, so you can't create an indeterminate number of formulas. you might need to create a function or if there is an upper limit of the number you can use that.
Either way, the way to access the variable is
shared datetimevar arr aDate;
aDate; //where i is the index that you are looking for.
Again, arrays are not my forte, hopefully, I am close enough for you to fill in the blanks.
HTH
|
Posted By: asadiq
Date Posted: 18 Nov 2009 at 6:33am
|
hi,
This is the code i am using.
local NumberVar bkglth := DateDiff('d',{Table.Start Date},{Table.End Date});
local NumberVar i := 0;
shared datetimevar array aDate;
Redim aDate[bkglth];
For i:= 0 to bkglth do
(
aDate:= dateadd("m",i,{Table.Start Date});
);
The error I am getting is "An array's dimension must be an integer between 1 and 1000"
Any ideas?
|
Posted By: lockwelle
Date Posted: 18 Nov 2009 at 6:36am
|
2 observations. 1) you are calculating the number of days between 2 dates and then adding the number of months.
2) you are not assigning the value to an array member but to the array, which is probably the cause of the error.
aDate := dateadd("m",i,{Table.Start Date});
should help
|
Posted By: lockwelle
Date Posted: 18 Nov 2009 at 6:38am
|
how annoying... id should say
aDate squareBracket i closeSquareBracket := dateadd("m",i,{Table.Start Date});
evidently it is seeing the array assignment as a code to display italics. Sorry for any confusion.
|
Posted By: asadiq
Date Posted: 18 Nov 2009 at 6:47am
|
local NumberVar bkglth := DateDiff('d',{table.Start Date},{Table.End Date});
local NumberVar i := 0;
local datetimevar array aDate;
Redim aDate[bkglth];
For i:= 0 to bkglth do
(
aDate:= dateadd('d',i,{Table.Start Date});
);
The new error is "A subscript must be between 1 and the size of array"
|
Posted By: lockwelle
Date Posted: 18 Nov 2009 at 6:56am
|
well, I said arrays weren't my forte... Unlike Basic, CR's array start at 1, not 0.
if you want the first array element to be the start date, change it to:
For i:= 1 to bkglth do
(
aDate sq i sq:= dateadd('d',i-1,{Table.Start Date});
); |
Posted By: asadiq
Date Posted: 18 Nov 2009 at 7:03am
|
local NumberVar bkglth := DateDiff('d',{Table.Start Date},{Table.End Date});
local NumberVar i := 0;
local datetimevar array aDate;
Redim aDate[bkglth];
For i:= 0 to bkglth do
(
aDate:= dateadd('d',i,{Table.Start Date});
);
Getting 2 errors now
1 - Array's dimension must be between 1 and 1000
2 - Subscript must be between 1 and array size
|
Posted By: asadiq
Date Posted: 18 Nov 2009 at 7:10am
|
i think i may have figured out why i am getting those errors but i am still getting a True or a False for each date rather than the date value itself.
Do you know how i can show the array now?
|
Posted By: lockwelle
Date Posted: 18 Nov 2009 at 7:24am
Redim aDate[bkglth];
For i:= 0 to bkglth do
(
aDate[i+1]:= dateadd('d',i,{Table.Start Date});
);
should help for the subscript, and if the dates are more than 3 years apart that would be the first error.
as for displaying the values, you could start with a simple formula:
shared datetimevar array aDate;
aDate[1]
it should display a date, hopefully the starting date...
|
Posted By: asadiq
Date Posted: 18 Nov 2009 at 7:32am
|
local NumberVar bkglth := (If {Tbl.StartDate}={Tbl.End Date} then 1 else (Tble.End Date}-{Table.Start Date}));
local NumberVar i := 1;
local datetimevar array aDate;
Redim aDate[bkglth];
For i:= 1 to bkglth do
(
aDate:= dateadd('d',i-1,{Tbl.Start Date});
);
i have changed the code a little bit so the array is not stuck at i=0 and I am not getting any errors now. However; it is returning a True/False and just one value.
I have tried aDate[1] and that does working giving me the Start Date which is good but as soon as I try aDate[2] or use aDate; it fails again.
Really appreciate all your help on this :):):)
|
Posted By: lockwelle
Date Posted: 18 Nov 2009 at 7:41am
|
I would check what value bkglth has. In some cases it is 1, so that is as high as you can go. I would also check if table.End Date - Table.Start gives the value that you are expecting (table and ( are incorrect above, but I am guessing that they just typos)...the true/false is confusing.
|
Posted By: asadiq
Date Posted: 18 Nov 2009 at 7:46am
|
You are right that in some cases bkglth is only 1 which is fine and I am getting the same value using a simple subtract operation between the 2 dates as I was getting with the Datediff and it is correct as what it should be between the dates.
I guess the last part is turning out to be the hardest where I need to figure out how to display the full array now rather than just one True/False
The TBL part does not matter as I am masking the original field names just for easy understanding.
Know any array experts here then??
|
Posted By: lockwelle
Date Posted: 18 Nov 2009 at 8:14am
|
displaying the entire array, is doable, but if you want to access them as a grouping variable or to use in a formula, it is going to be difficult, though not undoable, as I think about it....just not pretty.
to see the complete array in a textbox:
local numbervar ind;
shared datetimevar arr aDate;
local numbervar upInd;
local stringvar allDates;
upInd := uBound(aDate);
For ind := 1 to upInd Do
(
allDate := allDates + totext(aDate[ind],"MM/dd/yyyy") + chr(13)+chr(10);
);
allDate
drag the formula onto your report and set it to Grow on.
HTH
|
Posted By: asadiq
Date Posted: 18 Nov 2009 at 8:27am
|
Using this second formula is giving me an error to say " The ) is missing" but i cannot see where it is missing.
If I needed it to come out as a date array rather than a string; how complicated would that formula be then?
|
Posted By: asadiq
Date Posted: 18 Nov 2009 at 8:40am
|
Its done!!!!!!
It is finally working.
Thank you so much for all your help. Really great!!
:)
|
Posted By: asadiq
Date Posted: 18 Nov 2009 at 9:05am
|
it is the next phase now.
To get the date value rather than text value so i can display summaries for other fields based on this date grouping
|
Posted By: lockwelle
Date Posted: 19 Nov 2009 at 6:15am
|
if you just need the date, since it is a datetime array...
shared datetimevar aDate;
aDate[1];
you can of course use a variable...if this is something that is going to be grouped on, I would have another shared variable running that is the index to the aDate array, like:
shared datetimevar array aDate;
shared numbervar aDateIndex;
aDate[aDateIndex];
then in you group footer or somewhere increment aDateIndex like;
shared numbervar aDateIndex;
aDateIndex := aDateIndex + 1;
I don't know if CR will group on an array like this, it probably will as long as the array is 'defined'/populated before the grouping takes place (like in the report header as it is only called once per report.
HTH
|
|