Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mod Expression (2000)

    Hi,

    Current we use code to determine the following time equals hours.

    Mins Hr
    If 0'-30' = .5
    If 31'-60' = 1
    If 1.01'-1.30' = 1.5
    If 1.31'-2' = 2

    And we use the following codes:

    tmpA = IIf(IsNull(fBill![txtHours of direct service]), 0, fBill![txtHours of direct service] * 60)
    'assigning Minutes of service to Tmpb
    tmpB = IIf(IsNull(fBill![txtMinutes of direct service]), 0, fBill![txtMinutes of direct service])
    tmpC = tmpA + tmpB 'tmpC is total # of minutes
    tmpA = Int(tmpC / 60) '# hrs - convert back to hour, with no minutes
    tmpB = tmpC - (tmpA * 60) '# min left
    'calculate the 30 minute intervals
    tmpD = Int(tmpB / 30) '# of 30-min. intervals
    tmpE = tmpB Mod 30 '# min left Mod 30 - returns the remaining minutes
    If tmpE > 0 Then tmpD = tmpD + 1 'If mod min > 0 then # of 30 min intervals is rounded up
    If tmpD = 2 Then 'if tmpD = 2 then add another hour to tmpA and make tmpD=0
    tmpA = tmpA + 1
    tmpD = 0
    End If
    tmpF = tmpA + ((tmpD * 0.5)) '#hrs + (# of min intervals *.5)
    Rem ****tmpF is total hrs rounded to nearest 30 min as a decimal

    And now user want to change 30min to 45 min as decimal, please see below:

    Mins Hr
    If 0'-45' = .5
    If 45'-1.15' = 1
    If 1.16'-1.45' = 1.5
    If 1.46'-2' = 2

    Then I did the following changes in the code:

    tmpB = IIf(IsNull(fBill![txtMinutes of direct service]), 0, fBill![txtMinutes of direct service])
    tmpC = tmpA + tmpB 'tmpC is total # of minutes
    tmpA = Int(tmpC / 60) '# hrs - convert back to hour, with no minutes
    tmpB = tmpC - (tmpA * 60) '# min left
    'calculate the 45 minute intervals
    tmpD = Int(tmpB / 45) '# of 45-min. intervals
    tmpE = tmpB Mod 45 '# min left Mod 45 - returns the remaining minutes
    If tmpE > 0 Then tmpD = tmpD + 1 'If mod min > 0 then # of 45 min intervals is rounded up
    If tmpD = 2 Then 'if tmpD = 2 then add another hour to tmpA and make tmpD=0
    tmpA = tmpA + 1
    tmpD = 0
    End If
    tmpF = tmpA + ((tmpD * 0.5)) '#hrs + (# of min intervals *.5)
    Rem ****tmpF is total hrs rounded to nearest 45 min as a decimal

    But I only be able to get 0-45" works, but not the rest.

    Please help.

    Regards

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

    Re: Mod Expression (2000)

    What format is the underlying data in? Is it a time format? or a decimal format ? Is it a number of minutes?

    Where are you trying to perform this? in a Query? or in Code?

    Could you provide a little sample db?

    You say at one point you want it in 45 min intervals, but you also have:

    <hr>Mins Hr
    If 0'-45' = .5
    If 45'-1.15' = 1
    If 1.16'-1.45' = 1.5
    If 1.46'-2' = 2
    <hr>

    Why does the second one only go to 1.15 and not 1.30? etc.
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mod Expression (2000)

    Hi,

    It's a number of minutes, number of hours & number of minutes.

    I trying to perform this in code which is trying to change 30 mins intervals to 45 mins.

    Sorry for wrong time, below is current table:

    Mins Hr
    If 0'-45' = .5
    If 45'-1.30 = 1
    If 1.31'-2.15' = 1.5
    If 2.16'-3' = 2

    Sorry, since there are so many confidential data, so I can't provide the sample db. But the 30 mins intervals code is works. I just want to replace with 45 mins.

    Thank you so much for all of you help.

    Regards

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

    Re: Mod Expression (2000)

    Try
    <code>
    tmpA = Nz(fBill![txtHours of direct service], 0) * 60
    tmpB = Nz(fBill![txtMinutes of direct service], 0)
    tmpC = tmpA + tmpB - 1 'tmpC is total # of minutes - 1
    tmpF = (tmpC 45 + 1) / 2</code>

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mod Expression (2000)

    I am sorry, Hans. I am wrong. Below is the way that user would like to have:

    Mins Hr
    If 0'-45' = .5
    If 45'-1.15' = 1
    If 1.15'-1.45' = 1.5
    If 1.45'-2.15' = 2

    Basicly the first 0'-45' is consider .5 hr, then 30mins interval.

    I am really apologize for incorrect information.

    Please advice!

    Regards

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

    Re: Mod Expression (2000)

    Please provide clear and sufficient information.
    - Should 0' be converted to 0 or to .5?
    - Should 45' be converted to .5 or to 1?
    - Should 1.15' be converted to 1 or to 1.5?

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mod Expression (2000)

    Sorry for providing unclear information. Below is what I want to be onverted:

    0' should be converted to 0
    45' should be converted to .5
    1.15' should be converted to 1

    Mins Hr
    If 1'-45' = .5
    If 46'-1.15' = 1
    If 1.16'-1.45' = 1.5
    If 1.46'-2.15' = 2

    Sorry for the unclear information again.

    Regards

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

    Re: Mod Expression (2000)

    Try this:

    tmpA = Nz(fBill![txtHours of direct service], 0) * 60
    tmpB = Nz(fBill![txtMinutes of direct service], 0)
    tmpC = tmpA + tmpB
    If tmpC > 1 And tmpC < 31 Then
    tmpC = 31
    End If
    tmpF = ((tmpC + 14) 30) / 2

  9. #9
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mod Expression (2000)

    Thank you so much, Hans. It works.

    One more question, Maybe User will want to have the following change later:

    45' should be converted to 1
    1.15' should be converted to 1.5

    Mins Hr
    If 1'-44' = .5
    If 45'-1.14' = 1
    If 1.15'-1.44' = 1.5
    If 1.45'-2.14' = 2

    So how's the code should be used?

    Thank you so much for all your help.

    Regards

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

    Re: Mod Expression (2000)

    Change +14 to +15 in the code from my previous reply.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mod Expression (2000)

    Thank you so much, hans.

    Regards

Posting Permissions

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