Results 1 to 11 of 11
Thread: Mod Expression (2000)

20070123, 02:37 #1
 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 30min. 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 45min. 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 045" works, but not the rest.
Please help.
Regards

20070123, 06:58 #2
 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

20070123, 15:07 #3
 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

20070123, 16:10 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>

20070123, 22:07 #5
 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

20070124, 07:56 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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?

20070124, 16:55 #7
 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

20070124, 17:10 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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

20070125, 05:01 #9
 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

20070125, 07:38 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Mod Expression (2000)
Change +14 to +15 in the code from my previous reply.

20070125, 14:40 #11
 Join Date
 Feb 2006
 Posts
 504
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Mod Expression (2000)
Thank you so much, hans.
Regards