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!