# Thread: Mod Expression (2000)

1. ## 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. ## 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.

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Mod Expression (2000)

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

11. ## 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
•