1 2 Previous Next 23 Replies Latest reply on Jan 20, 2011 9:21 AM by cosmocanuck

    Trouble comparing dates



      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!



        • 1. Re: Trouble comparing dates

          The * char is a Jolly symbol, meaning zero or more characters.

          So, entering: <*/*/2011 while in Find Mode, may solve your problem

          • 2. Re: Trouble comparing dates

            Given that you see

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

            after your script has attempted to find records and failed, I'd take a close look at the script to see if you can find any reason why the value was entered three times. Also make sure that Date Received is of type date. (Sounds like it is, but I'm not going to assume that.)

            Here is one script example for searching for dates prior to 1/1/2011:

            Enter Find Mode[]
            Set Field [Donations::Date Received ; "< 1/1/2011"]
            Perform Find[]


            Enter Find Mode[]
            Set Field [Donations::Date Received ; "< 1/1/" & Donations::gYear ]
            Perform Find[]

            Donations::gYear would be a field with global storage set where you can specify the year before performing the script.

            • 3. Re: Trouble comparing dates

              Hi again! OK, in order to break it down to basics, I'm trying to just do a hand-typed Find command, no script involved. I still get the strange behaviour!

              I go into Find mode and enter just this in the Date Received field: 


              No quotes around it or anything...

              I hit "Enter" and... it tells me no records were found. When I click into the field, it expands to show that it now holds this text:

              >*/*/2011 */*/2011 */*/2011

              I think Marvin Gaye said it best: What's goin' on?

              For the record, it is a Date field, and I get the same results regardless of what date formatting I apply to it.

              If I put quotes around my query like so:


              Then I get the other dreaded error:

              "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”."

              So, I'm at a loss. I'm sure it's something that will turn out to be as plain as the nose on my face... and that's pretty plain, believe me.

              Anxiously awaiting further suggestions!


              • 4. Re: Trouble comparing dates

                Sorry, but as I break this problem down via ever-more-basic tests, it seems like the "real" problem I'm battling here has nothing to do with the date comparison at all. I'm getting errors even when I put a simple date in the Date Received field (i.e. telling FMP to just find records that match this date.)

                Let's say I hand-enter "2010" into the field. I expect it to find all donations received in 2010. But I get the error about no records found. And when I click "Modify find", the field shows:

                */*/2011 */*/2011 */*/2010

                Yep - three dates added, two of which aren't even the same year as what I typed in.

                I sure hope someone has run into this before... 



                • 5. Re: Trouble comparing dates

                  If I enter 2010, I only get */*/2010 if no records are found.

                  I wonder if your file is damaged. Try doing a recover on the file and see if the recovery reports a problem and then check to see if the recovered file behaves differently even if no problems are reported.

                  Best practice is not to use a recovered file but to replace your damaged file with an undamaged back up copy if possible. (You may have to import all data from your recovered file into a clone of your back up copy to get the most complete data possible.)

                  • 6. Re: Trouble comparing dates

                    I'm afraid that recovering and re-opening produced a new file with the same exact behaviour. (And no issues were found when recovering the file.)

                    • 7. Re: Trouble comparing dates

                      On other observation:

                      > */*/2011

                       will find zero records unless you have previously entered dates that are 1/1/2012 or later.

                      • 8. Re: Trouble comparing dates

                        Yes, I understand that... but I haven't actually been doing that search. When "*/*/2011" was put into the field, it was in response to my attempt to do a find on "2010". In fact, typing in "2010" caused it to (cryptically) show ">*/*/2011 */*/2011 */*/2011" in the field after I clicked "modify find".

                        Weird. I hope I'm able to solve this soon as I'm stalled on a project that's required by the end of the month - and I'm sure there are many other issues to solve after this one! Laughing

                        • 9. Re: Trouble comparing dates

                          My last trouble:

                          Is there a trigger that you gived to that field ?

                          • 10. Re: Trouble comparing dates

                            Copying and pasting from an earlier post:

                            I go into Find mode and enter just this in the Date Received field: 


                            Just making sure that putting in the wrong criteria for the results you want to get aren't a factor here.

                            I still think your file has a problem. Recover doesn't fix every problem. Either that or a script trigger is kicking in and you haven't spotted it. Try creating a completely new layout from scratch (Do not copy and paste from existing layout) and testing to see if you get different results when you try it on that layout.

                            You can also upload your file to a sharing site and post the download link here if You'd like someone to take a closer look at it.

                            • 11. Re: Trouble comparing dates

                              Nope, no script triggers on the field or the layout...

                              • 12. Re: Trouble comparing dates

                                Thanks, I will try re-creating the problem on a new layout - and if that doesn't lead anywhere I will post the file here. 

                                • 13. Re: Trouble comparing dates

                                  OK, no luck with a new from-scratch layout. So... here is the file! I'm super-grateful if anyone can have even a brief look at it.


                                  (Note: I had to control- or right-click and choose "Save File As...")

                                  To recreate the problem, switch to the Donations field and do a Find.

                                  Type   >2010   into the Date Received field. Or > */*/2009 . Both should produce records.

                                  Perform the search, which fails, and then click "Modify Find" in the dialog box to see what gets put into the field. For either of the above searches, it should be an unexplainable series of three dates in a row, with asterisks.

                                  Fingers are crossed that it's something incredibly obvious to everyone but me!


                                  • 14. Re: Trouble comparing dates

                                    I wish I had better news, but I've run this file forwards and backwards, created new date fields and even hand entered dates into them and each time I get the same results you did. My reluctant conclusion at this time is that this file is damaged in some way that Recover cannot detect...

                                    1 2 Previous Next