 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / September 2006Calculations based on time
I have a vStartTime and an vEndTime. I want to subtract one from the other, and then subtract 30 minutes unpaid time for lunch to see how many total hours a person worked in a day. I have tried unsuccessfully to use many variations of the =DateDiff function. >I have a vStartTime and an vEndTime. I want to subtract one from the other, >and then subtract 30 minutes unpaid time for lunch to see how many total >hours a person worked in a day. > >I have tried unsuccessfully to use many variations of the =DateDiff function. I think this expression would do that: (DateDiff("n", vStartTime, vEndTime) - 30) / 60  SignatureMarsh MVP [MS Access] Thank you so much for your answer. VARIABLES: MonStartW1 = 8:00 AM MonEndW1 = 5:30 PM So: MonStartW1- MonEndW1 = 9:30 When I put your suggestion: =(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 into my field, it returned "0:00". The goal is to subtract 30 minutes for unpaid lunch time from the total time. Ron > >I have a vStartTime and an vEndTime. I want to subtract one from the other, > >and then subtract 30 minutes unpaid time for lunch to see how many total [quoted text clipped - 5 lines] > > (DateDiff("n", vStartTime, vEndTime) - 30) / 60 The calculation is correct, but you are trying to use a time format on a floating point number not a date/time value. If you get rid of the text box's Format property, it should display the correct number of hours. SignatureMarsh MVP [MS Access] >VARIABLES: >MonStartW1 = 8:00 AM [quoted text clipped - 13 lines] >> >> (DateDiff("n", vStartTime, vEndTime) - 30) / 60 Marshall, you're the man. It works great. Thanks for giving your tiem to help us! Ron > The calculation is correct, but you are trying to use a time > format on a floating point number not a date/time value. If [quoted text clipped - 17 lines] > >> > >> (DateDiff("n", vStartTime, vEndTime) - 30) / 60 I have one additional challenge: Now I need to add the total hrs. worked each day along the bottom, which is in the function you gave me. I am having trouble assigning that value to a variable so I can add them up for the 7 days. Suggestions? Ron > The calculation is correct, but you are trying to use a time > format on a floating point number not a date/time value. If [quoted text clipped - 17 lines] > >> > >> (DateDiff("n", vStartTime, vEndTime) - 30) / 60 Shouldn't be a problem doing that in a text box, but you can not reliably use the values from calculated controls in VBA code. Can't you get by with another text box expression like: =txtMonHrs + txtTueHrs + txtWedHrs + . . . SignatureMarsh MVP [MS Access] >I have one additional challenge: Now I need to add the total hrs. worked >each day along the bottom, which is in the function you gave me. [quoted text clipped - 24 lines] >> >> >> >> (DateDiff("n", vStartTime, vEndTime) - 30) / 60 The only place I have the number of hrs for the day is in the calculated text box that has the DateDiff function in it. But you say I can't rely on a calculated box. So I am confused as to what the =txtMonHrs is referring to. Ron > Shouldn't be a problem doing that in a text box, but you can > not reliably use the values from calculated controls in VBA [quoted text clipped - 29 lines] > >> >> > >> >> (DateDiff("n", vStartTime, vEndTime) - 30) / 60 You asked about assigning the hours to a variable, my response was that "you can not reliably use the values from calculated controls in VBA code". Note the part about calculated controls and VBA code. Those are two different environments that run in different code "threads". Both environments are reliable, but mixing the two is not. Maybe all this confision is caused by me applying specific technical meaning to your vague general terminolgy, I can't tell how precisely you are using these words. txtMonHrs is the name name I made up for the text box **control** (not variable) that calculates the Monday hours. SignatureMarsh MVP [MS Access] >The only place I have the number of hrs for the day is in the calculated text >box that has the DateDiff function in it. But you say I can't rely on a [quoted text clipped - 36 lines] >> >> >> >> >> >> (DateDiff("n", vStartTime, vEndTime) - 30) / 60 Here are two days' boxes: Sunday time text boxes: Start Time: Name: SunStartW1, Control Source: SunStartW1 End Time: Name: SunEndW1, Control Source: SunEndW1 Calculation text box at bottom of Day: Name: Text8, Control Source: =(DateDiff("n",[SunStartW1],[SunEndW1])-30)/60 ============= Monday time text boxes: Start Time: Name: MonStartW1, Control Source: MonStartW1 End Time: Name: MonEndW1, Control Source: MonEndW1 Calculation text box at bottom of Day: Name: Text6, Control Source: =(DateDiff("n",[MonStartW1],[MonEndW1])-30)/60 ================ The goal is to get the two total times for the two days, which your DateDiff calculation does correctly, to add the total of the two days together for a total of hours worked. I hope this helps. Ron > You asked about assigning the hours to a variable, my > response was that "you can not reliably use the values from [quoted text clipped - 48 lines] > >> >> >> > >> >> >> (DateDiff("n", vStartTime, vEndTime) - 30) / 60 Translating my guesses at the text box control names to the names you are using, the expression would be: =Text8 + Text6 SignatureMarsh MVP [MS Access] >Here are two days' boxes: >Sunday time text boxes: [quoted text clipped - 77 lines] >> >> >> >> >> >> >> >> (DateDiff("n", vStartTime, vEndTime) - 30) / 60 That makes sense. When I do it, the text box is empty. No error, just empty. I tried formating it with: nothing (empty), fixed, and general. No errors, but the field is still empty. Any other ideas? Ron > Translating my guesses at the text box control names to the > names you are using, the expression would be: [quoted text clipped - 81 lines] > >> >> >> >> > >> >> >> >> (DateDiff("n", vStartTime, vEndTime) - 30) / 60 As long as Text6 and Text8 are in the form's footer section and displaying the correct values, it doesn't make sense for their sum to be blank. Are you sure the total text box is in the same section as the other calculated text boxes? Maybe the ForeColor and BaxkColor are the same??? The only other thought I have is that either it is taking a long time to calculate the total or that Access lost track of all the calculatioms it's supposed to do. Try clicking on the total text box and the hitting the F9 key to force a recalculation. SignatureMarsh MVP [MS Access] >That makes sense. When I do it, the text box is empty. No error, just >empty. I tried formating it with: nothing (empty), fixed, and general. No [quoted text clipped - 87 lines] >> >> >> >> >> >> >> >> >> >> (DateDiff("n", vStartTime, vEndTime) - 30) / 60 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.