8 Replies Latest reply on Sep 24, 2013 5:31 PM by LizMarks

    Finds based on date fields don't work

    LizMarks

      Summary

      Finds based on date fields don't work

      Product

      FileMaker Pro

      Version

      12 advanced EDU

      Operating system version

      Windows 7

      Description of the issue

      When I perform a Find on a Date field I get no records.

      I am concerned because this is a new copy of FileMaker and this is my second problem report and I just got it. My first problem was because I could not register the product from within Filemaker because there were no scroll bars on the registration window and I couldn't expand the window to fill all required fields.

      Steps to reproduce the problem

      Perform Find on Date field.

      I created 2 records with dates of 2/2/2011 and one with 11/11/2011.

      When I searched for 2/2/2011 no records were found and when I tried a date range of 1/1/2011...12/1/2011 no records were found.

      When I used the tutorial database included with the product I could not perform the Find on a date range either.

      Expected result

      3 records when searching for date range.

      Actual result

      none

      db3.jpg

        • 1. Re: Finds based on date fields don't work
          philmodjunk

               I search on date fields and with date ranges frequently with no trouble.

               Sounds like one of two possibilities:

               1) Perhaps Date Paid is not a field of type date. If it's of type text, this type of find won't work consistently.

               2) You are searching on records populated via a data import and the records were converted from text and/or from a different "locality" setting than the current file.

               From what I see in your screen shot, I would guess 1) is the most likely issue.

          • 2. Re: Finds based on date fields don't work
            LizMarks

                 The field is defined as a date field. The date range query does not work either.

                 I created a simple database with 3 records to test this problem because I ran into it when working through the Filemaker tutorials and the accompanying databases. 

                 Because of my registration problem and this one I am concerned that I might have bought bogus software although the serial number that came with it activated the product.

                 My system is a vanilla Windows 7 and I am a retired UNIX SE.

                  

                  

            • 3. Re: Finds based on date fields don't work
              philmodjunk

                   I really doubt that a problem registering the software is a factor here. I could see such a copy failing to open at all or failing to connect to a hosted database file as a client, but not just failing to search out records by date or date range.

                   The one unusual thing in your screen shot is the formatting of the date in the date field. Spaces delimit the month, day and year values instead of slashes. That should not be a problem, but it's not the default setting for a FileMaker date field either and one of the reasons why I asked if the field was really of type date and not a text field. Can you explain why they have that appearance?

                   Here's a link to the Known Bugs List database. Go to the Reports layout, enter find mode (don't click the find button on the layout) and try performing finds with criteria in the date fields, including a date range. Do they work in this file? https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip

              • 4. Re: Finds based on date fields don't work
                LizMarks

                     I used the above database. I selected "Bug Reports" from the top page.

                     Then I clicked in the "Date Added" field and chose "Find Matching Records".

                     Since you can only attach one file to each post I will be adding a second one.

                • 5. Re: Finds based on date fields don't work
                  LizMarks

                       Here is the screen capture of the results.

                  • 6. Re: Finds based on date fields don't work
                    Markus Schneider

                         I'm missing the dots in the screenshots (-:

                         Date range (my system uses dd.mm.yyyy!)

                         9.2013 -> will execute a search with *.9.2013

                         1.2.2013...4.7.2013 will find every record from .. to  (2 or 3 dots, doesn't matter)

                         2.2.2011 will find every record on 2.2.2011

                    • 7. Re: Finds based on date fields don't work
                      philmodjunk
                           

                                Then I clicked in the "Date Added" field and chose "Find Matching Records".

                           I'm a bit confused by that. Right clicking the date added field will bring that option up, but then it finds all records with that date. It never brings up the second screen shot shown. (And it does find all records with that date.) I can drop into find mode and check the criteria used by that option, but I won't get it in the form shown in your second screen shot. I'll get the complete date matching that of the record where I selected this option.

                           What happens if you click Find in the Status tool bar, enter a date and then click Perform Find?

                      • 8. Re: Finds based on date fields don't work
                        LizMarks

                             I found the cause of this problem. I had the short date format under Control Panel>Region and Language, Format as MM d yyyy instead of M/d/yyyy.

                             Can't remember why I originally did this!!

                             Thanks to everyone for their help.