6 Replies Latest reply on Dec 3, 2012 4:13 AM by katrina

    Performing a find in a "date" field and getting conflicting results -

    katrina

      Hello,

       

      I am performing a records find on a field that is a drop down calendar. When I find for week ending "12/1/2012" I am getting results for that December date and the January dates of "1/21/2012" and "01/21/2012". Same results when I perform the find for the January date - I get the December as well. This occures whether I click the date from the calendar box or just enter it in date format.

       

      I have been doing these same filemaker date finds within my payroll records for years and have never received conflicting results like this. No recent changes have been made to the layout.

       

      Any thoughts or suggestions as to why this may be occuring?

      Thank you.

       

      Katrina

        • 1. Re: Performing a find in a "date" field and getting conflicting results -
          LyndsayHowarth

          Dates are very much dependent on the operating system of the machine / server machine and the International settings of that machine. I reckon the most likely scenario is that someone has changed the region settings on the machine and some data has been entered in the wrong format.

           

          When a file is created or a file is cloned, the default date format of the file is the same as the machine. Is there any chance that the data has been imported into a cloned copy?

           

          Dates are also dependent on the field format... is it actually a date field? (I expect so or you wouldn't have a calendar)

           

          How are those dates entered? Perhaps something has changed here... like a new date being inserted in a new line where another date already exists in the field. (This would be pretty hard to do manually but could be achieved if the data is being imported or generated on the web or by script under certain circumstances.)

           

          Try exporting the data to excel... perhaps you will see something there.

           

          How are you entering the date criteria for the find? Try adding an '=' in front... what do you get then.

           

          If you add '//' to the layout as text then make the box it appears in at least 1 inch wide... what does it show in the Browse mode for the date today?

           

          - Lyndsay

          • 2. Re: Performing a find in a "date" field and getting conflicting results -
            katrina

            Hi Lyndsay – thanks for the suggestions.

             

             

             

            No data gets imported into these records.  Every week I create  new records to record payroll data, basically one record for each day.  I have been using this particular records file since January 1, 2012.

             

             

             

            I have a “date” field and a “week ending date” field, both are set up as  drop down calendars.  The date field appears to be doing the find correctly – my week ending date field is not.

             

             

             

            The only difference I can see between these fields in the layout inspector is that with the date field, when drop down calendar is chosen, the data format has no options available.  In my week ending field, with the drop down calendar chosen, the data formatting still says no options available, but  it does give me the number formats available.  If the format is chosen as “general” or “as entered”  I still get the same incorrect results.

             

             

             

            If I use the = operator in the week end field I still get the incorrect results in the find.

             

             

             

            This is so interesting, because I have been using this same layout with these two date fields for my weekly payroll for over 5 years now and have never had a find  be incorrect like this.

             

             

             

            When I exported the file to excel, my “date” field shows up as 11/30/2012 but the “week ending date” field shows up as 1212012.  Which would be why I am getting both the January & December dates. 

             

             

             

            I still do not know where my format issue is wrong in the layout though.  Maybe I should try deleting the field and then creating it again?

             

             

             

            I FOUND IT!

             

            In my manage database, the field was listed as a number, not a date.  When I changed it, the find came out correctly.

             

             

             

            Walking through your suggestions helped me find the problem.

             

            Thank you.

             

            Katrina

            • 3. Re: Performing a find in a "date" field and getting conflicting results -
              LyndsayHowarth

              Hi Katrina,

               

              I'm glad I helped you fix your problem.

              Maybe next time you could assign the credit for the 'Correct Answer" to the person who helps you rather than to yourself.

               

              - Lyndsay

              • 4. Re: Performing a find in a "date" field and getting conflicting results -

                That WAS one of Lyndsay's suggestions.  :-)

                • 5. Re: Performing a find in a "date" field and getting conflicting results -
                  keywords

                  Lyndsay, I'm being picky, but just to correct one thing. You state:

                   

                  Dates are also dependent on the field format... is it actually a date field? (I expect so or you wouldn't have a calendar)

                   

                  It IS possible to assign a drop down calendar fields other thah date fields; it is just that the entered contents will be treated as text, number, etc. As it happens, field format turned out to be Katrina's problem …

                  • 6. Re: Performing a find in a "date" field and getting conflicting results -
                    katrina

                    Sorry about that.  This was my first question on the forum and I am unfamiliar with the process.  Is this something I can change now?

                     

                    Katrina