Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Entering time in fractions (Access 2002/SP3)

    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! <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

    TIA

  2. #2
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    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

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    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.

    <pre>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
    </pre>


    ? 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.
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    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"?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Entering time in fractions (Access 2002/SP3)

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

    <code>Int(4*[FieldName])=4*[FieldName]</code>

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

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    Thanks guys. I am going on a business trip today, but will try your suggestions when I get back on Monday. [img]/forums/images/smilies/smile.gif[/img]

    Brent

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    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

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    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).
    Regards
    John



  9. #9
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    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.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    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.

  11. #11
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    Thanks, Hans.

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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    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.

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    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
    Regards
    John



  14. #14
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    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. [img]/forums/images/smilies/smile.gif[/img]

  15. #15
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering time in fractions (Access 2002/SP3)

    John,

    You have been a huge help! [img]/forums/images/smilies/smile.gif[/img] 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).

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •