Can‘t Find by Calculated Dates Without Using the TEXTTODATE Function (DB)

If you can't find by a date, you probably need to use the TEXTTODATE function in the calculation formula.

This information was provided by Claris Corporation on 16 March 1998, and incorporated into Apple Computer's Tech Info Library.

When specifying a date result in a calculation field, it is not enough to simply choose Date from the Format Result popup menu. You must also surround the specified date with quotes and the TEXTTODATE function. Using the incorrect formula will return a date which looks correct, but does not result in a successful Find.

Incorrect Date (Calculation, Date result) = IF('Period'="Q1&2","1/1/93","6/1/93")

Correct Date (Calculation, Date Result) = IF('Period'="Q1&2",TEXTTODATE("1/1/93"),TEXTTODATE("6/1/93"))

The NOW Function
The TEXTTODATE function does not work with the NOW function, causing an ERROR result. Therefore, it is not possible to find a date in a calculation field with the formula: NOW() which is often used as a way for auto-entering the date a record is created.

ClarisWorks 2.0 offers Entry Option features which include auto-entering the creation date into a date field, so the NOW function method is not required.

In ClarisWorks 1.0, a workaround is to create an additional calculation field called DateFind set to return a date result using the formula:


DateFind (Calculation, Date Result) = TEXTTODATE(DATETOTEXT('NowCalcFld'))

Published Date: Feb 18, 2012