12 Replies Latest reply on Nov 4, 2008 7:03 AM by TSGal

    DATES/DATES

    Martin1

      Title

      DATES/DATES

      Post

      :robotmad:Hi

       

      Following on my previous post in relation to searching within dates I have encountered a further problem.

       

      In creating a spreadsheet I want to search records within a range by month, I have tried 10/2008 for this month and 1/0/2008...31/10/2008 but this does not show any records. The frustrating part is I know they are there because I put them in !

       

      The date field is set to date rather than text but may have been text previously, would this have any effect ?

       

      I have even tried to find in 2 fields with one being case status which is a range 1 to 6 and then by date but then it find say all the 6's across all dates not the date range I put in.

       

      This is quite a similar problem to the one I had previously, I hope someone is able to help, I am extremely frustrated !!! :smileymad:

        • 1. Re: DATES/DATES
          blackcat795
            

          Martin..

           

           

           I have tried 10/2008 for this month and 1/0/2008...31/10/2008


          Is this a typo as what month is 0?

           

          Steve

          • 2. Re: DATES/DATES
            melindab
               Why not try */10/2008 for any in the month of October...
            • 3. Re: DATES/DATES
              clemhoff
                

              Hello Martin,

               

              if after conversion your date field contains valid dates and according to your date locale (dd/mm/yyyy)

               

              "*/{1...10}/2008" or "1/2008...10/2008" or "1/1/2008...31/10/2008"

               

              will find all dates from January through October, 2008

               

               

              Reading the online help has the power to avoid such a deep and intense frustration. :smileywink:

              - Finding ranges of information

              - Finding numbers, dates, times, and timestamps

               

              hth

              • 4. Re: DATES/DATES
                Martin1
                  

                Hi

                 

                Thanks for the ideas, I have tried all of the methods suggested but each with the same result.

                 

                Our system deals with individual policies issued, which can either be outstanding, closed or settled. The numerical equivalents for these are 1,2 and 6 respectively.


                When a policy is settled the data is upgraded and a payment is made. At the end of each month I want to run the report to get all payments, i.e. cases settled (6), made in that month.

                 

                My thought process has been to construct the report layout in such a way that it can be exported to excel but the problem lies in dragging the information into that report. When I enter 10/2008, or */10/2008 or 1/10/2008...31/10/2008 (sorry this was a typo vefore) I receive the no matching records box. I cannot see how this can be as I have inputted the dates in the fields myself and know they are there !

                 

                I don't suppose using a drop down calendar on the field would be detramental to what I am trying to achieve ?

                 

                Any further thoughts would be appreciated.

                 

                Many thanks

                 

                 

                 

                 

                • 5. Re: DATES/DATES
                  clemhoff
                    

                  Martin,

                   

                  ... just to check the validity of your "date" field contents ...
                  Do all your records return "1" if you add a calculated field (result: number) with the formula "isValid ( yourDateField)" ?

                  • 6. Re: DATES/DATES
                    Martin1
                      

                    Clem

                     

                    As far as I can see, and you will have to bear with me as I am a bit of a newbie on some elements of Filemaker, this calculation returns a ? against all the dates in that field ? Is there a reason why ?

                     

                    As I say I am not entirley sure that I have fully understood your query.

                     

                    Thanks

                    • 7. Re: DATES/DATES
                      davidhead
                         Another way to check the validity of dates is to perform a find with ? in the date field. This searches for invalid dates. I suspect all your records will be returned. What you actually want for such a search is no records found.
                      • 8. Re: DATES/DATES
                        clemhoff
                          

                        Martin,

                         

                        at the bottom of the calculation dialog box, there is a list value who lets you determine the output format of the calculation. The choice of this value must be set to either "Number" or "Text".

                         

                         

                        • 9. Re: DATES/DATES
                          Martin1
                            

                          Thanks I have performed this find with the ? and it returns no records found. Also on the calculation I did have number selected and if I change this to text it returns the ifDateValid.

                           

                           

                          • 10. Re: DATES/DATES
                            Martin1
                              

                            Hi

                             

                            I have finally managed to sort out the check you suggested and it returns the value 1 for all the records.

                             

                            I did seem to resolve this matter by puttinh the indexing to none on the date field. However, after doing this I started to experience a number of crashes so have had to turn this back to All ? Any ideas ???

                            • 11. Re: DATES/DATES
                              Martin1
                                

                              I think I may have established what the problem is with the date fields I am searching in. Although they are showing as being indexed if I right click and try to insert from the index nothing appears.

                               

                              I have made a copy of the field and made it + to the old field and an index appears. This meand I can perform the date find I require.

                               

                              Any ideas how to make the index work on the original field, as I say it is already switched on ????

                               

                              Any thoughts greatly appreciated.

                              • 12. Re: DATES/DATES
                                TSGal

                                Martin:

                                 

                                Thank you for your posts.

                                 

                                Since you are experiencing crashes with the original field (with index turned off) and the index now appears with a new field, this seems to point to the original field being corrupt in some manner.  I would remove the old field, and rename the new field back to the old field name.

                                 

                                Another option would be to recover the file (File -> Recover...) and see if the recovered file displays the index.

                                 

                                TSGal

                                FileMaker, Inc.