asked on

Converting listbox hours and listbox minutes to date/time format

I'm designing a form on which there will be elapsed time for an activity. I want to have two unbound list box controls, one for hours and another for minutes. In this particular application minutes will be reported only in 15 minute intervals. The hour list box will have 1, 2, 3, 4. The minute list box will have 00, 15, 30, 45. I'd like to have a third not visible control that is bound to a field in the table with regular date/time format. The conversion from the list boxes to the bound control will take place in the list boxes AfterUpdate events. What formula do I use for the conversion?

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockiroads

there is also timeserial that u can use

timeserial(lstHours, lstMinutes, 0)

returns the time

rockiroads

"Miriam, why is that a administrative comment then"
he asks cheekily :)

mbizup

Rocki: I don't know what you're talking about. ;-)

stevbe

I think you need to determine which order the controls need to be , or will be filled out in so you can update the invisible textbox value in the afterupdate event of the lstHours and/ or lstMinutes. Maybe do it for both so you can be sure to catch it no matter which order they enter the data in.

Private Sub lstHours_AfterUpdate()
Dim dtmElapsed As Date

'set date variable to 0 (12/31/1899 12:00 AM)
dtmElapsed = 0
DateAdd("h", Me.lstHours.Value, dtmElapsed)
DateAdd("n", Me.lstMinute.Value, dtmElapsed)
Me.txtEventEnd.Value = dtmEnd
End Sub

Private Sub lstMinutes_AfterUpdate()
Dim dtmElapsed As Date

'set date variable to 0 (12/31/1899 12:00 AM)
dtmElapsed = 0
DateAdd("h", Me.lstHours.Value, dtmElapsed)
DateAdd("n", Me.lstMinute.Value, dtmElapsed)
Me.txtEventEnd.Value = dtmEnd
End Sub

rockiroads

I swear, it showed up as admin comment.
I must be seeing things, surely u would never abuse your power :-D

Jim Horn

(Off Topic)

Rock: If Miriam's only badger-like problem here is inadvertently posting comments as Admin Comments, then she's doing pretty well. :)

WFBweb

ASKER

mbizup: Works fine...thanks! I changed the hours list box to 0,1,2,3. I made the default 0 and the default of the minutes list box 00. That way no matter what order they enter data in or if they skip one of the fileds becaue the default is ok, it works.

mbizup

Glad to help :-)

GRayL

For the sake of completeness, tbxInvisible control source:

=CDate(CStr(Me!lbxHours & ":" & Me!lbxMinutes) or

=TimeSerial(Me!lbxHours,Me!lbxMinutes,0)

GRayL

WFBweb, I'm not looking for anything, but didn't you have to prefix the listbox references with Me! or Me. ?

WFBweb

ASKER

I don't know if I had to, but I did it 'automatically' the first time I tried it (and with brackets around the control name).

mbizup

Ray,
The Me. prefix is needed for intellisense, and is better programming style, but is not needed to get the selected value.

GRayL

So I found out. I still think it behooves the 'Experts' to consistently display the 'better programming style'. I've been playing with Access for over 15 years now and just the other day I 'discovered' you can create a form with no controls, bind it to a table, and determine the value of every field in the table from code - as well as manipulate the table. While that may have been obvious to you, you could say it took my breath away. All the while I had thought you needed controls. And another one (strongly held belief) bites the dust - oh well!

rockiroads

Howdo Jim
just teasing of course :)
Anyways what u doing here, I thought u were taking a well deserved break