Problem re checking if a record exists

[NAColman]

I'm writing a function that does some calculations on dates, and need to check if a particular date has a matching date record in another unrelated table.

A pseudo-code description of what I'd like to do...

for Date := Start to Finish
Check := recordexists(key=Date) in TableB
if Check=true then do something
endfor

TableA (not seen in the example) would be the table (or joined tables) that "drive" the Crystal report.

Any ideas?

Thanks
Neil Colman

 

[HowardHammerman]

You will need to use SQL data or access your data using ODBC DSNs (data source names). Then you will create two
joins between tables A and B. In the first join, join the start date from table A to the date field in Table B. Select the join and pick 'join options' and select the 'greater than or equal to' optio.
Create a second join from the end date in table A to the date field in Table B, select options and pick "less than or equal to".

Understand, I never did this and I am sure it may be more complex. But give it a try.

Ken Hamady may have a better solution.

Of course you could do this with sub-reports. Link on the dates and in the sub-report have a count total that counts the records and passes the value back to the main report. Then create a formula in the main report that checks the shared variable and does what is required based on the number. If zero, then there is no match, etc. Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[NAColman]

Sorry, I should have given some more information about the problem...

The report returns information based on a single unjoined table - called TableA for this purpose.

TableA contains 2 date fields:
StartDate
EndDate

The actual type of this fields is DateTime, i.e. dd/mm/yyyy hh:mm:ss

TableB (not linked to TableA) contains 2 fields:
HolidayDate (DateTime)
HolidayDescription (Text)

The HolidayDate field again takes the form dd/mm/yyyy hh:mm:ss, but in all cases in this table, the time part of the value is set to 00:00:00. For example, a real value from this table would be 25/12/2002 00:00:00.

A function in the report then processes a loop running from StartDate to EndDate and checks to see if these 2 dates or any of those in between correspond to a record in TableB.

However, the StartDate and EndDate fields do not have 00:00:00 values for the time part of the field, so the comparision needs to be
if date(StartDate) = date(TableB.HolidayDate) then...

Any ideas?