Solved

fill in hours and minutes

Posted on 2013-12-29
10
376 Views
Last Modified: 2013-12-29
hey guys,

i can't believe i'm asking such a rudimentary question, but i guess it would be good to hear from yall how yall would most elegantly do it = )

i have a table which contains the work hours for a full day and a half day. at present, the full day is 8:15 AM --> which stands for 8 hrs and 15 min and the half day is 4:30 AM

now this of course doesn't make sense to the user.

i would like to create a form that when they open it, they will be able to 1) see the existing hrs and mins for a full day and half day, and 2) edit the hrs and mins.

i can definitely do this through VBA and SQL but i kinda think it's an over kill.

i'm thinking something along the lines of hidden text box, timevalue function and comboboxes something like that. perhaps default values properties and control sources as well.

but i can't seem to think of the elegant solution and i really want to learn how to do things elegantly instead of pulverise everything through code ha.

could yall kindly show me the way guys? and i apologise in advance if this is a question asked a number of times, i tried searching but perhaps i've got the keywords wrong. thanks in advance guys!! = ))
0
Comment
Question by:developingprogrammer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 85
ID: 39744621
I don't quite understand what you mean.

If "8:15" indicates 8 hours and 15 minutes, then just give them 2 textboxes prefilled with 8 and 15 respectively, and let them edit it. If they also need to edit the "half day" (which I assume is 4 hours and 30 minutes) then give them two textboxes prefilled with 4 and 30. When the user modifies any of those just write them back to the table.

However, I'm sure I'm misunderstood what you need, so please feel free to explain further.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39744637
If I understand correctly, you are using a date/time field to represent a duration (length of time), is that correct?  If so, I could see how that might be confusing to you and your users.

I tend to agree with LSM, if you are trying to capture the hours and minutes worked during a day, simply provide two textboxes, bound to fields [WorkedHours] and [WorkedMinutes].  Do not attempt to use a date/time field to record hours worked.  Attempting to sum these later will be extremely confusing.
0
 

Author Comment

by:developingprogrammer
ID: 39744650
hey LSMConsulting and Dale, thanks for your help!! = )

yes Dale you are right i'm using a date / time field to represent a duration - and unfortunately i can't change this to a numbers field because i'm inheriting a database from a very novice programmer (myself haha) and it's quite deeply ingrained already.

thanks for your help LSMConsulting = ) yes i would like to display the current date / time field in hrs and minutes to the user, and then let them edit it. if i were using textboxes filled with 8 and 15 respectively, these would be static values which doesn't reflect the current value in the date / time field.

i could always use VBA to get the hrs and the minutes to fill in the 2 textboxes, and when they click save i'll get the time value of these 2 textboxes and fill it into a hidden bound textbox.

however i'm trying to avoid VBA and see if i am "overthinking" the solution as yall have gently shared i usually do ha = P

how do yall think i should go about it guys given that i am using a date / time field to capture the duration (i won't do that moving forward = )    )
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 39744663
You're using a incorrect datatype to store the Duration, so I don't see how you'll resolve this without VBA. A "duration" is not a Date or Time - it's an Integer or Double value. You can store the Start and Stop times, and calculate that, but you can't use a single DateTime field to store the Hours and Minutes of the "duration".
if i were using textboxes filled with 8 and 15 respectively, these would be static values which doesn't reflect the current value in the date / time field.
Obviously you'd have to use VBA to fill those values with those from the underlying fields.
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 167 total points
ID: 39744677
You might be able to use two unbound textboxes for the Hours and Minutes, then have a bound (but hidden) textbox bound to your [Worked] date time field.

In the after update event of each of those two textboxes, you populate the [Worked] field like:

Private Sub txt_Worked_Hours_AfterUpdate

    me.txt_Worked = TimeSerial(me.txt_Worked_Hours, me.txt_Worked_Minutes, 0)

End Sub
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 166 total points
ID: 39744782
Except for the "AM" I really can't see your problem. There is absolutely no problem in using data type Date for a duration or time span except for the display of values exceeding 1 day and that can be easily solved.

Just apply a format of h:nn to the textbox bound to the field and it will show 8:15 or 4:30 which should make sense for the users.

/gustav
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39744787
Of course, you could use decimal hours for your users.

At OnCurrent fill an unbound textbox:

  Me!txtDecimalWorkHours = DateDiff("n", #00:00#, Me!WorkHours) /60

At the AfterUpdate event write back the value:

  Me!WorkHours = TimeSerial(0, Val(Nz(Me!txtDecimalWorkHours)) * 60, 0)

No fuzz.
0
 

Author Comment

by:developingprogrammer
ID: 39744840
hey guys, thanks for all your help and chipping in!

Dale i really like the after update event of the 2 unbound textboxes you suggested!! really nice = )

Gustav thanks!! i'm glad that i've got your encouragement in the data type! = ) hrmm i guess i can join what you shared with me together with Dale's solution in the future for any of such cases - thanks once again Gustav!! = ))
0
 

Author Comment

by:developingprogrammer
ID: 39744842
hi Scott, so sorry i mistook you as LSMConsulting!! ha i must be going bonkers already!! but thanks for your help on this!! = ))
0
 

Author Comment

by:developingprogrammer
ID: 39744846
oops LSMConsulting IS Scott McDaniel! how does the new naming system work? it displays name instead of nick? haha i'm confused! seems like i have to tackle one challenge at a time haha = P
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question