5 Replies Latest reply on Jan 28, 2017 9:08 PM by philmodjunk

    Strange date query problem


      I search often on date ranges (eg >2017/01/15 - my date settings yyyy/mm/dd). Always works fine.

      However, now I have a file where the queries constantly give the right records plus a set of "out of range" records.

      I have restarted FMP and even restarted my computer, but still the same problem.

      When I sort the records on the dates, the sorting is done properly and I see nothing strange.


      The file is made up of several smaller files from different sources (and therefore maybe different date formats), can that maybe cause something?

      And if so, how can I see if that would the problem? Again, I see n0thing strange and sorting works properly.


      I checked the forum on earlier reported bugs - only few old ones (am on FMP15).


      Anybody an idea? Thanks in advance!

        • 1. Re: Strange date query problem

          I presume that your system settings also specify the yyyy/mm/dd date format, yes? (That's the one I prefer, as well, and it's the international standard sequence under ISO 8601.) In the problem file, go to FMP's "File" menu, choose "Save a Copy As ..." of type "clone (no records)", and click on "Save".


          Save Clone.jpeg


          If the initial file is "XYZ.fmp12", the one you save will be called "XYZ Clone.fmp12". (You will later discard the former and rename the latter back to the original name.) The date formats in the cloned file will be set up to operate the same way as whatever your current system settings are.


          With XYZ (original) still open, make sure that you've selected "show all records" in every table within it. Then go into XYZ Clone and, for each table, used File > Import Records > File... > XYZ (with the matching names option as shown below) to specify that you want to import all the records from the corresponding table in XYZ. As part of this process, the dates should all be converted to the yyyy-mm-dd format.


          Matching Names.jpeg

          • 2. Re: Strange date query problem

            The cause of searching problem can be broken index.

            To rebuild index, set storage option of the field index to "none", (close file), then restore setting.

            • 3. Re: Strange date query problem

              OK thar sounds like a good idea.Going to try that out. Thanks for the help!


              • 4. Re: Strange date query problem

                Problem solved!


                First tried the simple solution of 19752 - rebuilding the indexes (with restart). Unfortunately didn't solve it, but thanks for thinking with me.


                Then tried Richard's (more work) solution. That 'solved it all. Thanks Richard!

                • 5. Re: Strange date query problem

                  FWIW, importing into a clone also rebuilds all indexes in the file.