Date Range Selection in Crystal Reports 8

[tomz1]

I am looking for a formula/record selection script/suppress script to do the following. Does anyone have any ideas? Thanks!

I have From & To Date/Time Ranges ranging from the past to the present and the future. For example, one possible date range is From 09/17/1998 To 12/31/1999, another possible date range is From 10/01/2001 To 10/01/2004, another possible date range is From 10/01/2004 To 10/01/2010. The From date is one field in a MicroSoft Access table & the To date is another field in the same table. I am looking to get the Billing amount (also a field in the same table) but I need to select the most recent and only the most recent date range record's Billing amount.

For instance:
EX 1)
Record 1 : From Date 09/17/1998 To Date 12/31/1999 BillAmount 100.00

Record 2: From Date 01/01/2000 To Date 12/31/2001
BillAmount 200.00

will return 200.00 ONLY!!!
_______________________________________________________
EX 2)
Record 1: From Date 10/01/1999 To Date 12/31/2001
BillAmount 300.00

Record 2: From Date 10/01/2002 To Date 12/31/2002
BillAmount 400.00

Record 3: From Date 01/01/2003 To Date 09/01/2004
BillAmount 500.00

Record 4: From Date 08/01/2005 To Date 08/31/2007
BillAmount 600.00

Record 5: From Date 04/01/2008 To Date 03/31/2010
BillAmount 700.00

will return 500.00 ONLY!!!
___________________________________________________________

EX 3)
Record 1: From Date 01/01/2011 To Date 01/01/2013
BillAmount 800.00

Record 2: From Date 02/01/2016 To Date 02/01/2018
BillAmount 900.00

Record 3: From Date 03/01/2020 To Date 03/01/2021
BillAmount 1000.00

will return 800.00 ONLY !!!
___________________________________________________________

I have sorted the records by FromDate descending. Is there some code that can handle all 3 example scenarios above?

 

[synapsevampire]

Fairly tricky, but doable.

You might create a SQL Expression containing a datediff number for each date, and then base the record selection formula->group on minimum({%datediffformula}) = {%datediffformula} on whatever grouping you're using (you didn't share what this EX grouping level was).

-k