Solved

fill in hours and minutes

Posted on 2013-12-29
10
369 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
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 84
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 47

Expert Comment

by:Dale Fye (Access MVP)
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 84

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 47

Accepted Solution

by:
Dale Fye (Access MVP) 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 49

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 49

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

803 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