4 Replies Latest reply on Jun 20, 2016 11:17 AM by DrNelson

    Help with date/time on a query

    Apprentice

      I have a query called Today's Absence report, that will automatically list everyone who has requested time off that has been approved (or everyone that submitted sick leave). I want to be able to pull a list that show absences for today.

      Ex: Joe Smith is off May 16th- 20th

           Sally Student is off May 20th

           Jane Doe is off May 20th-June 1st

           John Doe is off June 1st-June 3rd.

       

      I want the names to show up and drop off according to date only (even though on the form they can select a date and time). Is there a way to either default the time only or the best way to configure the query.

      Below is the time off form and below that is the criteria on the query. Right now the query kind of works it drops off people because the time defaults to 12am.

      time off.PNG

      query criteria.PNG

        • 1. Re: Help with date/time on a query
          Jenny.Lardh SupportEmployee

          Hi,

           

          Which date is it a problem with that it defaults to 12am? Is this the start date or end date?

          As far as I read your query, it will show everything where the start date is in the past and the end date is in the future and the User itself is at a certain Status.

           

          You can set the date/time field to only show date on the window, you do this in the properties in Window Manager. This way the user will only have to select a date. However, in the database there will still be a time automatically added, which will be 00:00:00.

          If the user selects start date: 15.06.2016, then this will be 15.06.2016. 00:00:00, meaning the beginning of the day and the full day will be taken into account.

          If the user selects an end date of: 20:06:2016, then this will be 20.06.2016 00:00:00 meaning the end of 19th, is this where the problem is?  As far as I can see from the query the end time might not matter as it's looking over a time span of 10 years, I assume just to be able to say "in the future".

           

          Could you give us a bit more details on exactly when the names aren't showing up in your query and what their times are set to?

           

          Kind Regards,

          Jenny

          • 2. Re: Help with date/time on a query
            Apprentice

            The times default to 12:00:00. Each morning the list runs that shows people who are out today. If someone just selects a date in the date/time field it defaults to 12:00:00 and those people do not show up on the list as being out today.

            • 3. Re: Help with date/time on a query
              Jenny.Lardh SupportEmployee

              Hi,

               

              So you mean that your date defaults to 12PM, as in middle of the day? So if someone selects today's date then they won't show in the query until after mid day?

              If this is the case, could you try setting your attribute to show date only on your window. The time should then default to 00:00:00 which will be midnight and should hopefully solve your problem.

               

              Jenny

              • 4. Re: Help with date/time on a query
                Apprentice

                For some reason the query doesn't show that person at all. Part of the problem is calculating whether a date is within the date range of an absence. Trying to get an absence to show up when running the June 20th report (when a person is listed as being out June 18th- June 22nd). Does that make sense? So maybe I need to figure out a calculation that determines if the date a person is out falls in the date a report is run?