fill in hours and minutes

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!! = ))
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Dale FyeOwner, Dev-Soln LLCCommented:
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.
developingprogrammerAuthor Commented:
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 = )    )
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Dale FyeOwner, Dev-Soln LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
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
Gustav BrockCIOCommented:
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.
developingprogrammerAuthor Commented:
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!! = ))
developingprogrammerAuthor Commented:
hi Scott, so sorry i mistook you as LSMConsulting!! ha i must be going bonkers already!! but thanks for your help on this!! = ))
developingprogrammerAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.