9 Replies Latest reply on Jun 6, 2014 10:44 AM by PhillipDziewior

    Unable to fine records using dates

    PhillipDziewior

      Title

      Unable to fine records using dates

      Post

           I am attempting to find records based on a date range and the results i get are records both in and out of the range I specify. I'm a newbie with FMP but this is becoming frustrating any help is greatly appreciated. I have a date field in my training table which is a date type field that I am attempting to use to find records of employees that have not yet completed training this year. I have attempted to use the find records function using less than */*/ 2014 and date range of 1/1/2014...6/2/2014 and omit records to see if I can see who has not yet completed training this year and i continue to get records of employees that have completed training between 1/1/2014 - 6/2/2014, any ideas?

            

           I have tried everyone's suggestions and still no luck when I attempt the range function I used 01/01/2014...06/02/2014 then selected omit records to have only the employee records of those who have not yet completed training this year but I continue to get more records than those requested. the date field is "date type" and not "text" and when I select just one specific date the correct records come up? could it be a relationship issue? as i have no relationship between an employee's # and date

        • 1. Re: Unable to fine records using dates
          WBSimon

               Not sure what your after here. Are you looking for dates before the year 2014? I

               n that case your find would be "<1/1/2014". if not, more information may be needed.

          • 2. Re: Unable to fine records using dates
            Markus Schneider

                 searching between:

                 first ... last (dots; 2 of them are ok - FileMaker will automatically replace '..' to '...')

                 Dates (please note: I got dd.mm.yyyy as date-format):

                 6.2014 -> searches all of june, 2014

                 2014 -> searches all of the year 2014

                 1.3.2014...15.5.2014 -> searches between

                 1.5.2014... -> searches all after 1.5.2014

                 ... 1.5.2014 -> searches all before

                 etc

            • 3. Re: Unable to fine records using dates
              philmodjunk

                   Open Manage | Database | Fields, find your fields that store dates and make sure that they are of type date and not text.

              • 4. Re: Unable to fine records using dates
                philmodjunk

                     Instead of modifying your original post, please use the misnamed "Post a new Answer" to post follow up replies. It's easy to miss new info added to the original post.

                     I suggest describing the design of your database in more detail. You mentioned relationships. This might be an issue. If you are on a layout based on Table A and you specify search criteria in a field from a related Table B, the results you get might not be what you expected.

                • 5. Re: Unable to fine records using dates
                  PhillipDziewior

                       The design of my database is three tables consisting of Employee data, training scores and equipment. The current layout is a tabbed layout, tab 1 is employee data, tab 2 is training scores and tab 3 is equipment. When I attempt the search that I want, which is simply to get the records of all employees that have not yet completed training between the dates of 1/1/2014 through 6/2/2014. In tab 1 I have a field designated as active or resigned and I select active so only active employees are included in the search I then go to tab 2 (training scores) and attempt the suggestions in this post to search the range of dates 01/01/2014...06/02/2014 but the data returned is not correct. This database also has a unique id number assigned to each employee I have named a SID (special id) and the only relationship I have created was a one to one relationship for the SID for each employee which is in all three tables. Any suggestions?

                  • 6. Re: Unable to fine records using dates
                    philmodjunk
                         

                              The current layout is a tabbed layout, tab 1 is employee data, tab 2 is training scores and tab 3 is equipment.

                         Am I correct that this layout is based on your Employee table? (if not sure, check Show Records From in Layout Setup...)

                         

                              tab 2 is training scores

                         And are these training scores recorded in a portal to a related table?

                         If so, exactly what data do you enter into the rows of that portal to document an employee's training?

                    • 7. Re: Unable to fine records using dates
                      PhillipDziewior

                           Yes, I checked as instructed and tab 1 is based on the employees table and tab 2 is the training table where the employee SID, training date, training type, score and pass/fail is recorded in colums with the same names. The employee SID has a one to one relationship for all 3 tables

                      • 8. Re: Unable to fine records using dates
                        philmodjunk

                             The tabs are not based on any tables. The LAYOUT is based on exactly one table. I would guess that this is the employees table.

                             Then on Tab 2, I would guess that you have a portal to a related table of records where each record records:

                             

                                  training date, training type, score and pass/fail

                             With one row of the portal used for each such entry.

                             But if you use a set of separate fields one set of fields to record each set of this training information--so that all of an employee's training info is recorded in a single record, then it may look like a portal but it will not work for your find.

                             I will assume for now that you do indeed have a portal to a related table here.

                             Reviewing your description of what you want to find:

                             

                                  I have a field designated as active or resigned and I select active so only active employees are included in the search I then go to tab 2 (training scores) and attempt the suggestions in this post to search the range of dates 01/01/2014...06/02/2014

                             it would appear that you select "active" in a field from the employees table tab 1, then enter a date range into the date field in the portal on tab 2. Then it would seem that you need to specify the value that indicates that they passed the training and then you'd select the "omit" option from the status area.

                             Is that exactly what you did?

                             That should work to list all employees that did not pass training in the specified date range, but it will include all non active employees. Is it possible that the inclusion of non-active employees are what is keeping this from working for you?

                             If so, you can return to Find mode, specify "Active" and then select "Constrain Found Set" to remove the non-active employees.

                              

                        • 9. Re: Unable to fine records using dates
                          PhillipDziewior

                               Thank you, utilizing the Constrain found set worked and gave me the records I needed. I do appreciate all the help you and the others have given thanks again.