hh:mm to minutes.

[DocBrown]

I want to create a text box where the user enters hours and minues as hh:mm,
where hours could be more than 24 hours. I want to convert that value to
minutes before saving it in my table. Is there a function that will do that
or do I need to parse the string myself and convert this to minutes?

Eventually, when the records are retrived, I will be summing the minutes
from records obtained from a query and then I will covert the minutes to
hhh:mm for display in another text box on a form.

Any tips on the way to do this is appreciated.

Thanks,
John

 

[Allen Browne]

I want to create a text box where the user enters hours and minues as
hh:mm, where hours could be more than 24 hours. I want to convert that
value to minutes before saving it in my table. Is there a function that
will
do that or do I need to parse the string myself and convert this to
minutes?

This example assumes you have an unbound text box named txtEnterTime, and
you want to assign the number of minutes to a field named Minutes. It
assumes that anything before the colon is hours (including the case where
there is no colon), and anything after it is minutes.

Private Sub txtEnterTime_AfterUpdate()
Dim varRaw As Variant
Dim varResult As Variant
Dim lngPos As Long
Dim bCancel As Boolean
Dim strMsg As String

varResult = Null 'initialize.
varRaw = Trim(Me.txtEnterTime)
If Not IsNull(varRaw) Then
'Locate the colon between hours and minutes.
lngPos = InStr(varRaw, ":")
If InStr(lngPos + 1, varRaw, ":") > 0 Then 'Multiple colons: bad.
bCancel = True
strMsg = strMsg & "Multiple colons not supported." & vbCrLf
Else
If lngPos = 0 Or lngPos = Len(varRaw) Then
'No colon, or trailing colon: whole number of hours.
varResult = CLng(Val(varRaw)) * 60
Else
'Leading colon: whole number of minutes.
If lngPos = 1 Then
varResult = CLng(Val(Mid(varRaw, 2)))
Else 'Colon in the middle: both hours and
minutes.
varResult = CLng(Val(Left(varRaw, lngPos - 1))) * 60 + _
Val(Mid(varRaw, lngPos + 1))
End If
End If
End If
End If

If bCancel Then
MsgBox strMsg, vbExclamation, "Invalid time entry"
Else
If Me.Minutes = varResult Then
'Do nothing
Else
Me.Minutes = varResult
End If
End If
End Sub

 

[DocBrown]

Yup, looks like doing the parsing is the way to go. This looks very good.

Thanks!
John

 

[DocBrown]

This is good, but I thinking I need one more tweek.

In a text box on a continuous form, what do I need to do to display the
minutes data in the DB as hh:mm and still allow the user to enter the time as
parsed below.

Thanks again!
John

 

[Allen Browne]

Put this txtEnterTime text box behind another text box that has properties:
- ControlSource: =[Minutes]\60 & Format([Minutes] Mod 60,"\:00")
- TabStop: No
- Name: txtHrMinutes
- On Got Focus: [Event Procedure]

Click the Build button beside that, and set it up so it gives focus to the
unbound text box:
Private Sub txtHrMinutes_GotFocus()
Me.txtEnterTime.SetFocus
End Sub


When the unbound text box gets focus, copy the time in so the user can
change it if needed:
Private Sub txtEnterTime_GotFocus()
'Put the right value into the entry text box.
If IsNull(Me.Minutes) Then
Me.txtEnterTime = Null
Else
Me.txtEnterTime = Me.Minutes \ 60 & Format(Me.Minutes Mod 60,
"\:00")
End If
'Activate the entry box.
Me.txtEnterTime.SetFocus
End Sub

Set the Tab Order (View menu) so the unbound box gets focus as you tab
through the form, and set it up so it is behind the display box
(txtHrMinutes): Tab Order on the View menu.

The neat trick is that, in a continuous form, the unbound entry box jumps in
front on the current row only; all the other rows still show the display box
(txtHrMinutes), so they look correct.

 

[DocBrown]

Hello Allen,

Wow, you're da man! That works great! I'm really surprised there's no easier
way to do that. It seems to be an incredibly obscure way to have to do this.

Thanks, Again!
John

Put this txtEnterTime text box behind another text box that has properties:
- ControlSource: =[Minutes]\60 & Format([Minutes] Mod 60,"\:00")
- TabStop: No
- Name: txtHrMinutes
- On Got Focus: [Event Procedure]

Click the Build button beside that, and set it up so it gives focus to the
unbound text box:
Private Sub txtHrMinutes_GotFocus()
Me.txtEnterTime.SetFocus
End Sub


When the unbound text box gets focus, copy the time in so the user can
change it if needed:
Private Sub txtEnterTime_GotFocus()
'Put the right value into the entry text box.
If IsNull(Me.Minutes) Then
Me.txtEnterTime = Null
Else
Me.txtEnterTime = Me.Minutes \ 60 & Format(Me.Minutes Mod 60,
"\:00")
End If
'Activate the entry box.
Me.txtEnterTime.SetFocus
End Sub

Set the Tab Order (View menu) so the unbound box gets focus as you tab
through the form, and set it up so it is behind the display box
(txtHrMinutes): Tab Order on the View menu.

The neat trick is that, in a continuous form, the unbound entry box jumps in
front on the current row only; all the other rows still show the display box
(txtHrMinutes), so they look correct.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This is good, but I thinking I need one more tweek.

In a text box on a continuous form, what do I need to do to display the
minutes data in the DB as hh:mm and still allow the user to enter the time
as
parsed below.

Thanks again!
John