Date Display 1

[camjam26]

Hey all,

I'm a relatively new user of CR9 and had some issues with dates that I'm trying to figure out. My company conducts audits of food plants (for example) and I've come across a somewhat difficult issue. As our audits can span across non-consecutive dates for just one audit, we need to be able to display the dates as such:

October 1, 2, and 3, 2004 or October 31 and November 2, 2004.

Dates are pulled from {Dates.InspectionDate}. Could someone please help out with this one? Your help is greatly appreciated.

Thanks,
Chris

 

[synapsevampire]

Sure, just give us access to the {dates.inspectiondate} table

When describing a database, one should include the fields, data types and example data to be thorough, the name alone won't help much, in fact that's the least important detail.

It sounds like you want to group your report by the audited entity id, and then place the date field in the details.

Hard to say though.

Try posting technical information.

-k

 

[camjam26]

Sorry about that...much learning I have to do obviously. I'll give it a shot...

I'm making a subreport for the dates to include in the final report so I'm only pulling from the Dates table. Included in the Dates table are only AuditInspectionID (string) and InspectionDate (DateTime). InspectionDate could include multiple dates for one inspection, such as:

7/7/2004 12:00:00AM
7/8/2004 12:00:00AM
7/9/2004 12:00:00AM
7/10/2004 12:00:00AM

Hope this helps a little. Let me know if more details are needed.

Thanks!
Chris

 

[camjam26]

Oh...and the way I need to format it as mentioned in my original post would be to display the dates as follows:

July 7, 8, 9, and 10, 2004

Due to the possibility of non-consecutive dates, that's the reason for the needed format. In addition, the audit could span across two months as follows:

July 31 and August 1, 2004

I originally formatted the dates using Min and Max to simply display the dates as July 7-10, 2004 for example. The non-consecutive dates threw a wrench into those plans though.

Thanks again,
Chris

 

[lbass]

I'm assuming you have grouped on {table.auditinspectionID} and that your subreport is placed in the group header or footer and linked to the main report on {table.auditinspectionID}. Then in the subreport create two formulas:

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x;

if onfirstrecord or
month({Dates.InspectionDate}) <>
month(previous({Dates.InspectionDate})) then
x := x + (if onlastrecord then " and " else "") +
totext({Dates.InspectionDate},"MMMM d") + ", " +
(if onlastrecord or year({Dates.InspectionDate}) <>
year(next({Dates.InspectionDate})) then
totext({Dates.InspectionDate},"yyyy")+", " else "") else
x := x + (if onlastrecord then " and " else "") +
totext({Dates.InspectionDate},"d") +", " +
(if onlastrecord or
year({Dates.InspectionDate}) <>
year(next({Dates.InspectionDate})) then
totext({Dates.InspectionDate},"yyyy")+", " else "");

//{@display} to be placed in the subreport report footer:
whileprintingrecords;
stringvar x;
left(x,len(x)-2);

In the subreport, suppress all sections except the report footer section.

-LB

 

[camjam26]

That worked great. Thanks very much for the help. Glad to have been pointed to this board.

Thanks again,
Chris

 

[camjam26]

Oh...sorry to be a pain again. Just noticed something when I was messing around with the dates. I inserted some test dates (July 29 and 31 and also August 2 and 3 - all in 2004) and got this result:

July 29, 31, August 2, and 3, 2004

Is there a way to modify the formula to make it appear as:

July 29 and 31 and August 2 and 3, 2004

Quite picky people we're running the reports for, but again your help is greatly appreciated.

Thanks one more time,
Chris

 

[lbass]

Okay, it took a while to work this out, and it seems like an awful lot of "and's", but this tests out here.

In the subreport insert a group on {Dates.InspectionDate} and choose "print on change of month." Then create three formulas:

//{@reset} to be placed in the subreport group header:
whileprintingrecords;
numbervar counter := 0;

//{@accum} to be placed in the subreport detail section:
whileprintingrecords;
stringvar x;
numbervar counter := counter + 1;
stringvar punct :=
if count({table.auditinspectionID},{Dates.InspectionDate},"monthly") >= 2 then
(if not(counter in [count({table.auditinspectionID},{Dates.InspectionDate},"monthly")-1,
count({table.auditinspectionID},{Dates.InspectionDate},"monthly")]) then
", " else
if counter = count({table.auditinspectionID},{Dates.InspectionDate},"monthly")-1 then
" and " else "");
stringvar yearx := if onlastrecord then
", " +
totext({Dates.InspectionDate},"yyyy") +", " else
if not onlastrecord and
year({Dates.InspectionDate}) <> year(next({Dates.InspectionDate})) then ", " +
totext({Dates.InspectionDate},"yyyy") else "";

if onfirstrecord or
month({Dates.InspectionDate}) <> month(previous({Dates.InspectionDate})) then
x := x + (if not onfirstrecord then " and ") + totext({Dates.InspectionDate},"MMMM d")
+ punct + yearx else
x := x + totext({Dates.InspectionDate},"d") + punct + yearx;

//{@display} to be placed in the subreport REPORT footer:
whileprintingrecords;
stringvar x;
left(x,len(x)-2);

You would then suppress all sections of the subreport except the report footer.

-LB

 

[camjam26]

Sweet - that is perfect. Works like a charm. Your help is more than appreciated.

Thanks again,
Chris