Closest Date Function Modification Help

[ccaines]

I am trying to use a function with a one sub function to return me the nearest date to the a date in the field in table. I have created the custom function in filemaker and have it working to the original specification/ design. The site that learn about the function says that if I modify the sub function comparative it can be forced to return me the nearest date earlier than my input date.

I am new to writing function and I could use and hand to modify this to work in that way.

Or if anyone has a better way of returning a record in a table with the nearest date before the input date it would be greatly appreciative.

I am using the ClosestDate Function from Brian Dunning Site below is the link

www.briandunning.com/cf/202

Other Information associated with Function:

This function is really 2 custom functions. The first sets up the variables and calls the sub function which is recursive and input of the value list is intended to be a related value list.

To help show what the function does imagine the following setup:

Table A has a date, a relationship is created to Table B which also has a date. Going from Table A to Table B is a one to many relationship.

The first parameter is the date in table A in which you are trying to find the closest date in table B related records. A vlaue list of the related dates from Table B is created and the name of this list is the second paramenter passed in the function.

Lest say the Date in Table A is 3/13/2005. The related dates are 1/1/2005, 1/15/2005, 3/1/2005, 3/14/2005. The function returns the date of 3/14/2005 as it the closest test date to 3/13/2005. In this case we have defined the closest date to be able to be either before or after the date being tested.

By modifying the subfunction compare statement the logic could be changed to force the closest test to have to be before . Using the same example above the function would return 3/1/2005. This function could be call PriorTest.

In addition it could be altered so the closest test would have to occur after the test date.

Edited June 28, 2012 by Lee Smith
Removed quoted Custom Function

[comment]

Or if anyone has a better way of returning a record in a table with the nearest date before the input date it would be greatly appreciative.

Why not use a relationship? If, for example, you define a relationship as:

MyTable::InputDate > Dates::Datefield

and sort the related Dates records by Datefield, descending, then the first related record in Dates will be the one with the nearest date before the input date.

See also the LookUpNext() function.