8 Replies Latest reply on Oct 21, 2013 2:35 PM by DranLang

    Date and Time Range

    DranLang

      Title

      Date and Time Range

      Post

           Good morning everyone!  I've been using FM now for 5 months and haven't encountered a range search until now.  One of our engineer from the other office wants to have a search button where in he wants to specify the date range and time range to search through hundreds and thousands of records with one click.  SO I set up a 2 calendar pulldown (1 for start date and 1 for end date) and 2 textbox where he can type in the Time start and Time end for his search which are all the 4 of them are set as global fields.   Then in my script I have :

           Enter Find Mode[]

           Set Field[TABLE::Date; TABLE::StartDate & ".."& TABLE::EndDate]

           Set Field[TABLE::Time; TABLE::TimeStart & ".." &TABLE::TimeEnd]

           Perform Find[]

            

           But this won't actually search properly if I specify a date range and time range close to the changing of days like the range for October 17, 2013 to October 18, 2013 where the time range is from 23:55:00 of October 17, 2013 to 00:10:00 of October 18, 2013.

           I'm not sure what's wrong or I'm missing some more steps in my script to have my search perform properly.  Is it also possible to combine the date with time in one textbox?

           Thanks everyone, any help/ ideas is deeply appreciated.

            

            

            

        • 1. Re: Date and Time Range
          philmodjunk

               I don't see why your script would fail if the Date and time fields are both of the correct data type.

               Are you sure that 10 minutes after midnight on October 18 shouldn't actually be 10 minutes after midnight on October 19 and thus the expected record wasn't found?

               Is it also possible to combine the date with time in one textbox?

               Well, it wouldn't be a field of type text, but you can replace your date and time fields with a single timestamp field that combines date and time in the same field.

               Then you could use:

               Set Field [ Table::TimestampField ; Timestamp (TABLE::StartDate ; TABLE::TimeStart ) & "..." & TimeStamp ( Table::EndDate ; Table::TimeEnd ) ]

          • 2. Re: Date and Time Range
            DranLang

                 I did checked and have verified that there are some records that falls in to the given range,  but for some reason my search (script) couldn't find them.

            • 3. Re: Date and Time Range
              philmodjunk

                   And are the date fields truly of type date and the time fields truly of type time?

                   Can you post an example of a record that failed to be found and the ranges that were specified for it?

              • 4. Re: Date and Time Range
                DranLang

                     I tried to copy (ctrl+prtscn) and paste (ctrl+v) here the sample but I can't seem to paste it.  Is there another way to copy the screen and paste it here?

                • 5. Re: Date and Time Range
                  DranLang

                       I just tried using the timestamp approach but results exceeds the actual number of records that should be displayed.  I specified that my range should be from April 7, 2013 at 23:59:00 to April 8, 2013 at 00:10:00 and in my script I used :

                       Set Field [ Table::TimestampField ; Timestamp (TABLE::StartDate ; TABLE::TimeStart ) & "..." & TimeStamp ( Table::EndDate ; Table::TimeEnd ) ]

                        

                       I also verified that my TimestamField is of type TimeStamp, my StartDate and EndDate of type DATE and TimeStart and TimeEnd is of type TIME.  The search result started from April 7, 2013 at 23:59:00 but it further than April 8, 2013 at 00:10:00.

                  • 6. Re: Date and Time Range
                    philmodjunk
                         

                              I tried to copy (ctrl+prtscn) and paste (ctrl+v)

                         Instead, paste the copied image into Windows Paint (or any other image editor that you might choose), crop/edit it as needed and save it as a png file. Then use the upload controls below Post a new Answer to upload the image to the forum.

                         

                              The search result started from April 7, 2013 at 23:59:00 but it further than April 8, 2013 at 00:10:00.

                         Please post some examples of this. I want to see the exact values that were found that exceeded the specified range.

                    • 8. Re: Date and Time Range
                      DranLang

                           Thanks a lot Phil!  I got to work, it was actually the data that were imported earlier that caused the errors. I used the approached you suggested (the use of TIMESTAMP) for the field that combines the date and time.