I can see how you could use the IF() function to 'filter' for the records where the date can be identified as being in the range but I can't see how you could successfully work the date as a Primary key link.
Dates can be quite a challenge due to the several different internal formats that are possible and which are not obvious when looking at a date displayed on the screen, it being an interpretation of some underlying data formatted for presentation purposes.
If anyone does have a solution, or even an idea for an approach to the problem, I would love to hear about it.
Let's see if I'm envisioning this correctly...
You have a lookup table DATES (either a DB or a report) that has essentially this structure:
997 01/01/06 01/31/06
998 01/07/06 02/14/06
999 01/21/06 02/28/06[/font][/quote]and the other report COSTS possibly like:
997 01/05/06 990.00
998 03/01/06 800.00
999 01/21/06 700.00 /font[/quote]If this is correct, big if, using an external lookup I would bring into your COSTS model both the Start Date and the End Date fields where the Case number is your lookup field.
Then you can add a calculated Character field named "Service Date". The formula would be:
"")[/font][/quote]This would give you a date if the Case Date was within the date span, and nothing if the Case Date was outside the date span.
Edited to line up the headings a bit.
I have a lookup table DATES (either a DB or a report) that has essentially this structure:
CASE # START DATE END DATE------ amount -
997 01/01/05 12/31/06 $300
997 01/01/03 12/31/04 $250
997 01/01/00 12/31/02 $150
998 01/01/05 12/14/06 $500
998 01/01/03 12/31/04 $400
999 01/21/06 02/28/06 $50
and the report CLAIMS like:
CASE # CASE DATE amount----
997 01/05/06 ?
997 01/01/03 ?
998 01/21/06 ?
998 01/01/04 ?
If you know the answer a second problem is if the end date is blank meaning through current date. My initial table from the dates report references this as an invalid value and I cann't use it in a calculated field. I quess my downside is I can filter these out and handle them seperately.
If you know the answer a second problem is if the end date is blank meaning through current date. My initial table from the dates report references this as an invalid value and I cann't use it in a calculated field. I quess my downside is I can filter these out and handle them seperately. /b[/quote]From what I can tell (not tested but in principle) Kruncher's suggestion and formula should work unless you End Date is blank/reported as an error.
To deal with that you could change the IF formula to check the date it is exists or otherwise assume that if blank (or error 'message') then the date is OK. I assume that the case date cannot be after today.
An alternative approach would be to pre-handle the End Date issue using a calculated field that takes the end date if there is one or enters and acceptabel date - TODAY() for example? - if there is not. Then do the comparison with this 'work' field rather than the original. You may also want to consider whether you need to highlight that this was the approach used when outputting the results. It might seem a bit odd if a lot of claims occured on what appeared to be he last day of the range!