Solved

fill in hours and minutes

Posted on 2013-12-29
10
363 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
Comment Utility
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)
Comment Utility
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
Comment Utility
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 166 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now