Entering time in fractions (Access 2002/SP3)

Topic

New Reply

I’m working on a database that requires the user to enter time in fractions such as .25, .5, .75 and whole numbers such as 1, 2, etc. They also can enter times such as: 1.25, 1.5, 1.75.

I’ve noticed that there are many entries that looks like: .254, .245, 1.78 etc. due to typo errors. Since they run reports on how many hours of contact they have had with the clients, the hours must be accurate but with the typo’s, the results look like this: 109.39459639 because one single record has .254…

Does anyone has an idea of forcing Access to accept only .25, .5 , .75, 1, 1.25, 1.5, 1.75 and so on?

I can’t exactly create a combo box because we are talking about 24 hours PLUS fractions!

TIA

Reply | Quote

Replies

Brent,
I’ve found that most people have a hard time converting time to decimals. I’ve overcome this by having them input into 2 separate text boxes: 1 for hours, 1 for minutes. Default for both is zero. In your case it seems that minutes are in intervals of 15, i.e. .25, .5. .75. If that is always the case, you could make the minutes a list box (or combo box with appropriate Not In List event). Also, if you want to go further you could go the “start time”/”end time” and calculate from there. Good luck.
Kathi

Reply | Quote

Brent,
I’ve found that most people have a hard time converting time to decimals. I’ve overcome this by having them input into 2 separate text boxes: 1 for hours, 1 for minutes. Default for both is zero. In your case it seems that minutes are in intervals of 15, i.e. .25, .5. .75. If that is always the case, you could make the minutes a list box (or combo box with appropriate Not In List event). Also, if you want to go further you could go the “start time”/”end time” and calculate from there. Good luck.
Kathi

Reply | Quote

I agree with the suggestion that you split data entry into two, so yopu could use combo boxes.

however hee is a function that will check your data as it is.

Function fnchecktimes(timeToCheck As Single) As Boolean

Dim strtime As String
strtime = timeToCheck
If InStr(timeToCheck, ".") > 0 Then
        strtime = Right(strtime, Len(strtime) - InStrRev(strtime, "."))
        'Debug.Print (strtime)
        If (strtime  "5") And (strtime  "75") And (strtime  "25") Then
            fnchecktimes = False
        Else
            fnchecktimes = True
        End If
Else
            fnchecktimes = True
End If
End Function

? fnchecktimes(4.25)
True
? fnchecktimes(7.6)
False
? fnchecktimes(5.)
True

You could put in the before update event, and display a message box and set cancel = true if the function returns false.

Reply | Quote

Sorry. I was out of town and just came back today.

If I split the hours and minutes into two columns, then wouldn’t it be more painful to try to sum up the time? We run a VERY complicated CrossTab Query that separates service types then sum up total hours for each service type a month. I’d rather to avoid modifying the crosstab query because it was a PITA to set up .

Could we somehow code it so that when we choose hours and minutes in separate combo boxes and somehow merge them and store it in single column called “Service Hours”?

Reply | Quote

You can set a Validation Rule for the field in the design of the table:

Int(4*[FieldName])=4*[FieldName]

with the correct field name substituted. Don’t forget to enter an informative Validation Text too.

Reply | Quote

Thanks guys. I am going on a business trip today, but will try your suggestions when I get back on Monday.

Brent

Reply | Quote

The customer wants the combo box option rather than typing in the fractions. However, I quickly tested your (Hans) suggestion and it worked perfectly. I’ll use it as a “fall-back” plan if I can’t figure out how to combine the combo boxes into one single data in the column called “Total Hours”.

Thanks everyone for your suggestions.

Brent

Reply | Quote

I was in the middle of writing a description of how to do it with unbound combo boxes, when I noticed the word ‘column’ in your previous post.

Is this happening in a continuous form?

If so you can’t use unbound controls . (each record will always show the same value).

Reply | Quote

Actually, I was at a loss of words. Smile.

By “column”, I meant the field in the table. There is a field called “TotalHours” and it stores hours and its fractions such as 1, 1.25, etc. and by using separate combo boxes, I’d be forced to create two fields called Hours and Minutes, making it extremely difficult for me to do calculations on these fields.

I’d rather to have one field that stores both hours and minutes, I’ve found one solution but it works only with dates. If you could help me figure out how to combine two “unbound” combo boxes into one field, that’d be great.

Reply | Quote

Create a combo box cboHours and a combo box cboQuarters.
Leave the Control Source property for both blank (i.e. they are unbound.)
Set the Row Source Type property for both to Value List.
Set the Row Source for cboHours to 0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23
Set the Row Source for cboQuarters to 0;1;2;3
Set the Limit to List property for both to Yes.

Create code for the form as follows:

Private Sub cboHours_AfterUpdate()
UpdateTime
End Sub

Private Sub cboQuarters_AfterUpdate()
UpdateTime
End Sub

Private Sub Form_Current()
If IsNull(Me.TotalHours) Then
Me.cboHours = Null
Me.cboQuarters = Null
Else
Me.cboHours = Int(Me.TotalHours * 24)
Me.cboQuarters = (Me.TotalHours * 96) Mod 4
End If
End Sub

Private Sub UpdateTime()
If IsNull(Me.cboHours) Or IsNull(Me.cboQuarters) Then
Me.TotalHours = Null
Else
Me.TotalHours = Me.cboHours / 24 + Me.cboQuarters / 96
End If
End Sub

The After Update events of the combo boxes set the value of the TotalHours field. The On Current event of the form sets the combo boxes according to the value of TotalHours when the user moves to another record.

Reply | Quote

Thanks, Hans.

One quick question. Where do I store the code for Private Sub UpdateTime()?

Reply | Quote

In the form module, where you also enter the event procedures. Just copy it here and paste it into the module, or type it in yourself.

Note: you can place any code you need into a form module, you are not restricted to event procedures only.

Reply | Quote

In the form module, where you also enter the event procedures. Just copy it here and paste it into the module, or type it in yourself.

Note: you can place any code you need into a form module, you are not restricted to event procedures only.

Reply | Quote

Thanks, Hans.

One quick question. Where do I store the code for Private Sub UpdateTime()?

Reply | Quote

Create a combo box cboHours and a combo box cboQuarters.
Leave the Control Source property for both blank (i.e. they are unbound.)
Set the Row Source Type property for both to Value List.
Set the Row Source for cboHours to 0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23
Set the Row Source for cboQuarters to 0;1;2;3
Set the Limit to List property for both to Yes.

Create code for the form as follows:

Private Sub cboHours_AfterUpdate()
UpdateTime
End Sub

Private Sub cboQuarters_AfterUpdate()
UpdateTime
End Sub

Private Sub Form_Current()
If IsNull(Me.TotalHours) Then
Me.cboHours = Null
Me.cboQuarters = Null
Else
Me.cboHours = Int(Me.TotalHours * 24)
Me.cboQuarters = (Me.TotalHours * 96) Mod 4
End If
End Sub

Private Sub UpdateTime()
If IsNull(Me.cboHours) Or IsNull(Me.cboQuarters) Then
Me.TotalHours = Null
Else
Me.TotalHours = Me.cboHours / 24 + Me.cboQuarters / 96
End If
End Sub

The After Update events of the combo boxes set the value of the TotalHours field. The On Current event of the form sets the combo boxes according to the value of TotalHours when the user moves to another record.

Reply | Quote

What data type is the field ‘totalHours’ ? Is it a Date/time field in a time format, or just a single number?

It is slightly simpler to use single precision numbers, because you can add them (for reporting) without them getting converted into days when the number of hours exceeds 24.

Hans’ solution assumes it is date/time. Here is an alternative if it is just a number.

Create a combo box cboHours and a combo box cboQuarters.
Leave the Control Source property for both blank (i.e. they are unbound.)
Set the Row Source Type property for both to Value List.
Change cboQuarters to two columns, with the first column having zero width. This column holds the actual value, the second displayed column holds the number of quarters.
Set the Row Source for cboHours to 0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23
Set the Row Source for cboQuarters to 0;0;.25/;1;.5;2;.75;3
Set the Limit to List property for both to Yes.

Create code for the form as follows:

Private Sub cboHours_AfterUpdate()
UpdateTime
End Sub

Private Sub cboQuarters_AfterUpdate()
UpdateTime
End Sub

Private Sub Form_Current()
If IsNull(Me.TotalHours) Then
Me.cboHours = Null
Me.cboQuarters = Null
Else
Me.cboHours = Int(Me.TotalHours )
Me.cboQuarters = Me.TotalHours mod 1

End If
End Sub

Private Sub UpdateTime()
If IsNull(Me.cboHours) Or IsNull(Me.cboQuarters) Then
Me.TotalHours = Null
Else
Me.TotalHours = Me.cboHours + Me.cboQuarters
End If
End Sub

Reply | Quote

John,

Thanks. I didn’t realize that Hans’ solution had assumed I would be using Date/Time format. I was using Number format for the field.

Reply | Quote

John,

Thanks. I didn’t realize that Hans’ solution had assumed I would be using Date/Time format. I was using Number format for the field.

Reply | Quote

John,

You have been a huge help! I have few issues that I need your help with:

1) When you said to do this: Set the Row Source for cboQuarters to 0;0;.25/;1;.5;2;.75;3 and when I set the first column width to zero, the drop-down list shows 0,1, 2, 3 instead of .25, .5, .75. Is this intentional? Also, is the slash next to .25 a typo? Also, why do I need the “hidden” values of 0,1,2,3 when I don’t need it for cboHours? (I’m interested in learning how this process works)

2) When I enter this: “Hours: 1, Minutes: 0”, the TotalHours field shows 10 hours instead of 1.0? (I’m gonna try adding a period next to 0 which will work…) (EDIT: This worked. No need to worry about it anymore.)

3) When I enter something like “Hours: 1, Minutes: .25”, the TotalHours field shows 1.25, great…Then when I advance a record then go back one record or more to correct my previous entries, it shows combo boxes as “Hours: 1, Minutes: 0” but the TotalHours field shows 1.25. I’d like for the cboMinutes to continue showing the minutes I chose rather than resetting to zero (the cboHours does not reset even if I choose something like 3).

Reply | Quote

Brent

Following the sample that Hans provided, I set the second combo to be a quarters combo. It would also make sense to have it as a minutes combo, in which case it would need to be modified a bit.

Row Source 0;0;.25;15;.5;30;.75;45 (yes the / in my previous post was a typo)

The first column of the combo holds the values to be stored : 0,.25,.5,.75 , while the second column holds the values to be displayed: 0,15,30, 45.

So if you choose “hours: 1 minues: 15” the actual value stored is 1.25 because 15 mins is one quarter of an hour.

For comboHours, the value you see is the value you want stored, so it only needs one column.

Point 2 (which you say you have fixed) suggests that the two values are being put together as pieces of text rather than as numbers. Set the format property of each combo box to a numerical one.

The oncurrent code is supposed to ensure that when you revisit old data, both the combo boxes show the correct values. So either this code does not execute, or does not execute correctly. If you look at the propertries of the form, is there code in the oncurrent event?

Reply | Quote

That teaches me a valuable lesson in NEVER work late in the night ! After waking up after a good night’s sleep, now your suggestions make a lot of sense. I was too tired to notice that you were trying to suggest that I display the minutes in the conventional way (and using Hans’ example).

Also, was too tired to notice that indeed, one bit of code was missing “txt” (txtTotalHours) and that was what caused it to act strangely.

I’ve changed both combo boxes to Fixed format, do I need to change the table properties to Fixed too? (I can’t remember if I had set the Total Hours column to Fixed or not, it is at someone’s PC right now)

Many, many thanks for your help.

Reply | Quote

That teaches me a valuable lesson in NEVER work late in the night ! After waking up after a good night’s sleep, now your suggestions make a lot of sense. I was too tired to notice that you were trying to suggest that I display the minutes in the conventional way (and using Hans’ example).

Also, was too tired to notice that indeed, one bit of code was missing “txt” (txtTotalHours) and that was what caused it to act strangely.

I’ve changed both combo boxes to Fixed format, do I need to change the table properties to Fixed too? (I can’t remember if I had set the Total Hours column to Fixed or not, it is at someone’s PC right now)

Many, many thanks for your help.

Reply | Quote

Brent

Following the sample that Hans provided, I set the second combo to be a quarters combo. It would also make sense to have it as a minutes combo, in which case it would need to be modified a bit.

Row Source 0;0;.25;15;.5;30;.75;45 (yes the / in my previous post was a typo)

The first column of the combo holds the values to be stored : 0,.25,.5,.75 , while the second column holds the values to be displayed: 0,15,30, 45.

So if you choose “hours: 1 minues: 15” the actual value stored is 1.25 because 15 mins is one quarter of an hour.

For comboHours, the value you see is the value you want stored, so it only needs one column.

Point 2 (which you say you have fixed) suggests that the two values are being put together as pieces of text rather than as numbers. Set the format property of each combo box to a numerical one.

The oncurrent code is supposed to ensure that when you revisit old data, both the combo boxes show the correct values. So either this code does not execute, or does not execute correctly. If you look at the propertries of the form, is there code in the oncurrent event?

Reply | Quote

John,

You have been a huge help! I have few issues that I need your help with:

1) When you said to do this: Set the Row Source for cboQuarters to 0;0;.25/;1;.5;2;.75;3 and when I set the first column width to zero, the drop-down list shows 0,1, 2, 3 instead of .25, .5, .75. Is this intentional? Also, is the slash next to .25 a typo? Also, why do I need the “hidden” values of 0,1,2,3 when I don’t need it for cboHours? (I’m interested in learning how this process works)

2) When I enter this: “Hours: 1, Minutes: 0”, the TotalHours field shows 10 hours instead of 1.0? (I’m gonna try adding a period next to 0 which will work…) (EDIT: This worked. No need to worry about it anymore.)

3) When I enter something like “Hours: 1, Minutes: .25”, the TotalHours field shows 1.25, great…Then when I advance a record then go back one record or more to correct my previous entries, it shows combo boxes as “Hours: 1, Minutes: 0” but the TotalHours field shows 1.25. I’d like for the cboMinutes to continue showing the minutes I chose rather than resetting to zero (the cboHours does not reset even if I choose something like 3).

Reply | Quote

What data type is the field ‘totalHours’ ? Is it a Date/time field in a time format, or just a single number?

It is slightly simpler to use single precision numbers, because you can add them (for reporting) without them getting converted into days when the number of hours exceeds 24.

Hans’ solution assumes it is date/time. Here is an alternative if it is just a number.

Create a combo box cboHours and a combo box cboQuarters.
Leave the Control Source property for both blank (i.e. they are unbound.)
Set the Row Source Type property for both to Value List.
Change cboQuarters to two columns, with the first column having zero width. This column holds the actual value, the second displayed column holds the number of quarters.
Set the Row Source for cboHours to 0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23
Set the Row Source for cboQuarters to 0;0;.25/;1;.5;2;.75;3
Set the Limit to List property for both to Yes.

Create code for the form as follows:

Private Sub cboHours_AfterUpdate()
UpdateTime
End Sub

Private Sub cboQuarters_AfterUpdate()
UpdateTime
End Sub

Private Sub Form_Current()
If IsNull(Me.TotalHours) Then
Me.cboHours = Null
Me.cboQuarters = Null
Else
Me.cboHours = Int(Me.TotalHours )
Me.cboQuarters = Me.TotalHours mod 1

End If
End Sub

Private Sub UpdateTime()
If IsNull(Me.cboHours) Or IsNull(Me.cboQuarters) Then
Me.TotalHours = Null
Else
Me.TotalHours = Me.cboHours + Me.cboQuarters
End If
End Sub

Reply | Quote

Actually, I was at a loss of words. Smile.

By “column”, I meant the field in the table. There is a field called “TotalHours” and it stores hours and its fractions such as 1, 1.25, etc. and by using separate combo boxes, I’d be forced to create two fields called Hours and Minutes, making it extremely difficult for me to do calculations on these fields.

I’d rather to have one field that stores both hours and minutes, I’ve found one solution but it works only with dates. If you could help me figure out how to combine two “unbound” combo boxes into one field, that’d be great.

Reply | Quote

I was in the middle of writing a description of how to do it with unbound combo boxes, when I noticed the word ‘column’ in your previous post.

Is this happening in a continuous form?

If so you can’t use unbound controls . (each record will always show the same value).

Reply | Quote

The customer wants the combo box option rather than typing in the fractions. However, I quickly tested your (Hans) suggestion and it worked perfectly. I’ll use it as a “fall-back” plan if I can’t figure out how to combine the combo boxes into one single data in the column called “Total Hours”.

Thanks everyone for your suggestions.

Brent

Reply | Quote

Thanks guys. I am going on a business trip today, but will try your suggestions when I get back on Monday.

Brent

Reply | Quote

You can set a Validation Rule for the field in the design of the table:

Int(4*[FieldName])=4*[FieldName]

with the correct field name substituted. Don’t forget to enter an informative Validation Text too.

Reply | Quote

Sorry. I was out of town and just came back today.

If I split the hours and minutes into two columns, then wouldn’t it be more painful to try to sum up the time? We run a VERY complicated CrossTab Query that separates service types then sum up total hours for each service type a month. I’d rather to avoid modifying the crosstab query because it was a PITA to set up .

Could we somehow code it so that when we choose hours and minutes in separate combo boxes and somehow merge them and store it in single column called “Service Hours”?

Reply | Quote

I agree with the suggestion that you split data entry into two, so yopu could use combo boxes.

however hee is a function that will check your data as it is.

Function fnchecktimes(timeToCheck As Single) As Boolean

Dim strtime As String
strtime = timeToCheck
If InStr(timeToCheck, ".") > 0 Then
        strtime = Right(strtime, Len(strtime) - InStrRev(strtime, "."))
        'Debug.Print (strtime)
        If (strtime  "5") And (strtime  "75") And (strtime  "25") Then
            fnchecktimes = False
        Else
            fnchecktimes = True
        End If
Else
            fnchecktimes = True
End If
End Function

? fnchecktimes(4.25)
True
? fnchecktimes(7.6)
False
? fnchecktimes(5.)
True

You could put in the before update event, and display a message box and set cancel = true if the function returns false.

Reply | Quote