Add time in access using 2 fields

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • DJRhino1175
    New Member
    • Aug 2017
    • 221

    Add time in access using 2 fields

    I have a field called [StartTimeOfProd uction] (manually enter)
    I have a calculated field that gives me the actual production time. I need to take [StartTimeOfProd uction](6:30 am Format) and say add [Runtime](in minutes say 29.78) and get a [EndTimeOfProduc tion]

    I have been searching the net and can't seem to find an answer for this.

    Thanks for the help,

    DJRhino
  • twinnyfo
    Recognized Expert Moderator Specialist
    • Nov 2011
    • 3653

    #2
    Hey DJ,

    First, you have an intriguing problem. Usually, we know the start and stop dates/times and have to calculate the RunTime. Yours is going in the opposite direction. However, the solution will be similar.

    If your RunTime is always in a number of minutes, you should be able to use the DateAdd() function to resolve this. Your arguments would look something like this:

    Code:
    [EndTimeOfProduction] = DateAdd("n", [Runtime], [StartTimeOfProduction])
    That should get you into the ballpark. As long as you are working with actual Date fields, this should work.

    Hope that hepps!
    Last edited by twinnyfo; Mar 6 '18, 06:36 PM. Reason: typo

    Comment

    • DJRhino1175
      New Member
      • Aug 2017
      • 221

      #3
      I put this as my Code:
      Code:
      EndTimeOfProduction: DateAdd("n",[ProductionTime],[StartTimeOfProduction])
      Now I get #Name?

      Comment

      • twinnyfo
        Recognized Expert Moderator Specialist
        • Nov 2011
        • 3653

        #4
        Where are you putting this code? Is this on a Form? Are you running script from a VBA module behind a Form? Something else?

        Comment

        • DJRhino1175
          New Member
          • Aug 2017
          • 221

          #5
          I added this to a query that is feeding my form.

          Comment

          • twinnyfo
            Recognized Expert Moderator Specialist
            • Nov 2011
            • 3653

            #6
            Very strange....

            I just validated using this in a sample query and it ran fine.

            What data types are you using for [ProductionTime] and [StartTimeOfProd uction]? And these are fields in your Table?

            Comment

            • DJRhino1175
              New Member
              • Aug 2017
              • 221

              #7
              [ProductionTime] is a calculated field from info entered into the table. [EndtimeOfproduc tion] is in the table currently entered manually. in medium time format

              Comment

              • DJRhino1175
                New Member
                • Aug 2017
                • 221

                #8
                This is how [ProductionTime] is in the query:
                Code:
                ProductionTime: ([GoodMeters]/[CalcLineSpeed]+(Nz([Scrap])/[CalcLineSpeed]))
                [GoodMeters] & CalcLineSpeed] are also calculated fields.
                Last edited by DJRhino1175; Mar 6 '18, 06:37 PM. Reason: Added more info.

                Comment

                • twinnyfo
                  Recognized Expert Moderator Specialist
                  • Nov 2011
                  • 3653

                  #9
                  How is ProductionTime calculated? Is it taking two separate values of minutes and adding them or it is taking two Date/Time values and returning another Date/Time?

                  Either way, I've tried multiple permutations and haven't been able to generate either an error or #Name? result.

                  Comment

                  • twinnyfo
                    Recognized Expert Moderator Specialist
                    • Nov 2011
                    • 3653

                    #10
                    You read my mind..... What are some of the values that ProductionTime gives you? This is troublesome that you are experiencing this error, when I am unable to duplicate such an error.

                    We may be missing something obvious.

                    Comment

                    • DJRhino1175
                      New Member
                      • Aug 2017
                      • 221

                      #11
                      GoodMeters = [PartLength]*[QtyParts]/1000
                      CalcLineSpeed = [LineSpeed]*0.3048
                      ProductionTime: ([GoodMeters]/[CalcLineSpeed]+(Nz([Scrap])/[CalcLineSpeed])) - Which gives me a time in minutes in decimal form (25.45)
                      I want to take those minutes and added them to the [startTimeOfProd uction] which is in medium time format (6:30 am)

                      Hope this clears it up a little.

                      Comment

                      • twinnyfo
                        Recognized Expert Moderator Specialist
                        • Nov 2011
                        • 3653

                        #12
                        You say
                        I want to take those minutes and added them to the [startTimeOfProd uction] which is in medium time format (6:30 am)
                        But, is [startTimeOfProd uction] a "Date" data type?

                        However, even when I use a text field of "6:30 am" in my sample query and add 21 minutes, I still get "6:51 am"!

                        There must be something that we are missing in your query that is preventing this from working. I am trying to break my query and it refuses to give me an error. You are trying to make yours work and it refuses to give the results you want.

                        (Goofy scratching his head.....)
                        Last edited by twinnyfo; Mar 6 '18, 06:52 PM. Reason: typo

                        Comment

                        • DJRhino1175
                          New Member
                          • Aug 2017
                          • 221

                          #13
                          StartTimeOfProd uction = Date/Time in the table I'm pulling from

                          Comment

                          • twinnyfo
                            Recognized Expert Moderator Specialist
                            • Nov 2011
                            • 3653

                            #14
                            Bad news: I've recalculated everything just like you have described and my Query still works:

                            [IMGNOTHUMB]https://bytes.com/attachments/attachment/9387d1520362870/djrhino.jpg[/IMGNOTHUMB]

                            Code:
                            SELECT tblDJRhino.PartLength, tblDJRhino.QtyParts, tblDJRhino.LineSpeed, tblDJRhino.Scrap, tblDJRhino.StartTimeOfProduction, [PartLength]*[QtyParts]/1000 AS GoodMeters, [LineSpeed]*0.3048 AS CalcLineSpeed, ([GoodMeters]/[CalcLineSpeed]+(Nz([Scrap])/[CalcLineSpeed])) AS ProductionTime, DateAdd("n",[ProductionTime],[StartTimeOfProduction]) AS EndTimeOfProduction
                            FROM tblDJRhino;
                            Not sure what the issue is here..... Again, we gotta be missing something obvious.
                            Attached Files
                            Last edited by twinnyfo; Mar 6 '18, 07:05 PM. Reason: inserted image inline

                            Comment

                            • DJRhino1175
                              New Member
                              • Aug 2017
                              • 221

                              #15
                              The query does have two tables on it that are related many to 1, but the fields I'm using are on just the one table. This query is linked with a form(subform) to another main form. Kind of a complicated little database.

                              Comment

                              Working...