Date range/format help 1

[texusgirl]

Hey Everyone!

Here's my situation. I am using Crystal 8.5 and creating a subreport. The subreport has an orderdate(datetime) and an expiredate(datetime). What I am looking for is a way to create the report that will return the orderdate thru expire date in mm/yy format with a line for each month. Example orderdate 3/1/2005; expiredate 2/28/2006. The result I am looking for is

3/05 ------- --------- ---------
4/05 --------- -------- ----
5/05
6/05

etc... to the expire date 2/06.
If anyone can help I sure would appreciate it!
Thanks,
texusgirl

 

[Madawc]

You can turn dates into months using Month({your.date}), and then group using the month. Does that help?

Madawc Williams (East Anglia, UK)

 

[texusgirl]

Madawc,

Thanks, but that doesn't increment the month year for me. I need a formula that will increment the month year from the orderdate to the expiredate. I have an orderdate value and expiredate value. I need to have crystal increment from begin(orderdate) to end(expiredate) in mm/yy format. I had used DateAdd("m",1,{orderdate}) which gives me the next value needed,just don't know how to keep it going, especially when I hit a new year value??? Thanks in advance for your help.
texusgirl

 

[lbass]

Group on order date and select "Print for each month". You can then right click on the groupname field ->format field->date->customize tab->set day to "none".

To establish the range for the subreport, go to report->edit selection formula and enter your criteria. If you are requiring both date fields to fall within the same range, then use:

{table.orderdate} in date(2005,03,01) to date(2006,02,28) and
{table.expiredate} in date(2005,03,01) to date(2006,02,28)

-LB

 

[texusgirl]

Lookin good, just a snag on the range??? The Orderdate and expiredate will vary. Therefore, I won't know what their values are from record to record. I guess that's why I don't see the datevalue purpose??

thanks again,
texusgirl

 

[lbass]

How will they vary? Are you using parameters for the date range? The group on date will work regardless of the date range. If you are using parameters for the date range, then build that into the record selection formula and then link the subreport parameters to the main report parameters, using the dropdown list for the subreport to select {?dateparm} instead of the default {?pm-?dateparm}.

-LB

 

[texusgirl]

They vary because I have 4 subreports all together, all are linked to the main report by the field quoteno. Are you saying I should link this one by quoteno & orderdate? I have the subreport grouped on orderdate...and formatted correctly. Also I selected "Print for each month". Still I am getting just the one date field...which is the orderdate. My confusion is that it is not printing for each month, just the first orderdate. I assume I must enter a date range as you mentioned above, but if each quoteno has different orderdate & expiredates I wouldn't be able to input the date values in the formula????

 

[lbass]

If each quoteno has only one orderdate and expiredate, if you link the subreport on the quoteno field, you will only get the corresponding set of dates. I think you need to describe your intent and the structure of your main report (what your groups are, etc.), and also what you want each subreport to do. It might be that you need to change your linking. Also explain what it is you want to show per month.

-LB

 

[texusgirl]

It's a new day...I will get this figured out! The main report and all subreports are working fine. This is a new subreport that I am adding to th main. I think my links are fine as you stated, each quoteno will have the same date range. My intent for this subreport is to print each month/year that falls between the orderdate and expiredate. I guess like an array or range? Example: For a quote, the orderdate is 3/1/2005; the expiredate is 2/28/2006; I will need to print each month/year starting with the orderdate 3/05 and ending with the expiredate 2/06,

with each mo/yr on a separate line...like so:

3/05
4/05
5/05
6/05
7/05.... until it reaches the expiredate 2/06. I need to start with the orderdate and continue with each month/year until I hit the expiredate. The only other item on the subreport are some lines for user's to make notes.

3/05 _____ ______ _______
4/05 _____ ______ _______
etc...
Possibly a do while statement???
Thanks for your help...I don't think I was real clear before. It just seems like it should be a simple task, I'm just new to crystal.

texusgirl

 

[synapsevampire]

I'm guessing that what you're trying to state is that you wish to have a complete set of month/years, even if the data itself doesn't include all months.

This is a common request, and relatively simple.

Replace the currentdate and currentdate+70 with startdate and expiredate:

whileprintingrecords;
numbervar MonthCount:= datediff("m",currentdate,currentdate+70)+1;
numbervar Counter;
datevar Start:= cdate(year(currentdate),month(currentdate),1);
Start:=dateserial(year(Start),month(Start)-1,1);
stringvar array TheDates;
redim TheDates[MonthCount];
For Counter := 1 to MonthCount do(
TheDates[Counter]:=totext(dateadd("m",Counter,Start));
);
join(TheDates,chr(13))

That should get you close.

-k

 

[texusgirl]

synapsevampire,

When I run the subreport I get

"A string can be at most 254 characters long".?? Any ideas?
Thanks again,
texusgirl

 

[synapsevampire]

That is because CR 8.5 only supports 254 characters as output in a formula.

You'll need to break it down if you have more than 254 characters as output.

whileprintingrecords;
numbervar MonthCount:= datediff("m",currentdate,currentdate+70)+1;
numbervar Counter;
datevar Start:= cdate(year(currentdate),month(currentdate),1);
Start:=dateserial(year(Start),month(Start)-1,1);
stringvar array TheDates;
stringvar array TheDates2;
redim TheDates[MonthCount];
For Counter := 1 to MonthCount do(
if len(join(TheDates,chr(13))) < 220 then
TheDates[Counter]:=totext(dateadd("m",Counter,Start));
);
join(TheDates,chr(13))

This will allow you to test if the output is what you expect.

How many months are you expecting to span as the max?

BTW, later versions of Crystal don't have this limitation.

-k

 

[texusgirl]

Hey that is so close to the results that I wanted! The only thing is it is a string therefore, I can't format field options for date/time. I get

3/1/2005
12:00:00 AM
4/1/2005
12:00:00:00 AM etc...

I think 12 months will be the max that I would need to report on. Thanks to everyone on the help...it's not fun being stumped! If you have any suggestions on converting this formula field to date/time...let me know. I have a feeling it is another limitation.

thanks again,
texusgirl

 

[synapsevampire]

Try changing this line:

TheDates[Counter]:=totext(dateadd("m",Counter,Start));

to

TheDates[Counter]:=totext(dateadd("m",Counter,Start),"MM/dd/yy");

If that exceeds the number of characters that you need let us know.

-k

 

[texusgirl]

Worked like a dream! Thanks to everyone...
synapsevampire....this made my day!:-D

texusgirl