AnsweredAssumed Answered

Trouble comparing dates

Question asked by cosmocanuck on Jan 17, 2011
Latest reply on Jan 20, 2011 by cosmocanuck


Trouble comparing dates


Hi all! I'm a relative newbie to FMP (using version 10) whose confidence just took a hit when I tried to do a (seemingly) simple date comparison.

The project I'm working on is for a charitable organization; thus I have a table called Donations, which includes a field called Date Received. That field is set as a date field, with "Control style" set to a Drop-Down Calendar and the format as MM/DD/YYYY (with a slash as the Numeric Separator).

I have a script within which I want to find all records in the Donations table where the Date Received field is prior to the current year. But whether in the script, or just doing a live Find, I hit a wall.

If I go into Find mode and click the field, and type "< 2011", and hit Enter, I get a message "No records match this criteria". (But there definitely are records where this field has a date from 2010.)

Further weirdness: if I click "Modify Find" at that dialogue box, the Date Received field fills with this:

<*/*/2011 */*/2011 */*/2011

So it seems to understand that I'm just looking for any date before 2011... but why does it put it in there three times?

Alternatively, I enter this text into the Date Received field:

< Date (01;01;2011)

Which brings on this dreaded message:

"The value of this field must be a valid date in the range of years 1 to 4000 and should look like “12/25/2003”.  "

Particularly baffling is the fact that in a little "button experiment", which just produces a Custom Dialog, this script (in the "Message" area when setting up the dialog) produces a correct judgment as to whether the Date Received is from the prior or current year:

If ( Donations::Date Received > Date(12;31;Year(Get(CurrentDate))-1) ; "Donation is from the current year." ; "Donation is from a previous year." )

Why does the date comparison seem to work fine here, but not in a Find?

I've done a lot of searching and Googling to no avail... hope that somebody can nudge me in the right direction!