Check if date (in 1st database) is an holiday (defined in 2nd database

[Utreg]

Hi,
I tried to create a check for holidays in my report (CR V12).
In my main report I use the database: MainGroup.ArrivalTime [type is 'Date Time']

In another database (type is 'STRING') the holiday days are specified;

HolidayHours.ID HolidayHours.HolidayDate
1 2009-04-13
2 2009-05-21
3 2009-06-01
4 01-01
5 12-25
6 12-26

I would like to build a check like; IF MainGroup.ArrivalTime IN HolidayHours.HolidayDate THEN ..

I've created a subreport to fill up an array named "Holidays", but all values are 'TRUE'.
Problem is HolidayHours.HolidayDate should be converted to a correct date (with for this year all 'years = 2009').

// subreport
local numbervar countholidaydays;
countholidaydays = Maximum({HolidayHours.ID}); // result = 6 = correct
Shared DateVar Array holidays;
Redim holidays[countholidaydays];
Local numbervar i;
For i := 1 To countholidaydays Do
( if Datepart("yyyy",cdate({HolidayHours.HolidayDate})) <> 0
then holidays = cdate({HolidayHours.HolidayDate})
else holidays = cdate(DateAdd("yyyy", Datepart("yyyy", CurrentDate),cdate({HolidayHours.HolidayDate})));
)

Tips "How to fill an array 'Holidays' with the following data" are welcome;
2009-04-13
2009-05-21
2009-06-01
2009-01-01
2009-12-25
2009-12-26
And how can I use the created 'Date' array to check "IF MainGroup.ArrivalTime IN Holidays THEN .."
Thanks

 

[Madawc]

Do a left-outer link from the main table to the holiday table. When the holiday-table value is null, the day is not a holiday.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Madawc]

Note that Crystal will happily link tables in different databases. I've done that a lot.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IanWaterman]

Since your holiday table is a date string you can link to main table which is datetime.

I have done this in past by using a hidden subreport in report header which populates a shared datevar array. YOu can then reference reference this array in your main report.

If your holidays are datestime strings use the function

DTSToDate ("2000/01/13 11:30:15") to convert to true date

If they are not datetime you may have to break down to numbers and use date

Date(tonumber(right(datestr, 4)), tonumber(mid(datestr,4,2)), left(tonumber(datestr, 2)))

Ian