Answered by:
Mod function in VBA
Question

I think I have a faulty understanding of how the MOD function is used with decimal values.
The practical requirement I have to enforce is a time entry application where a user inputs on a form text box the amount of time they spent on an activity. Decimal hours are permitted, but the smallest level of precision is supposed to be a quarter hour (0.25 hours). So the user can say he spent .25 hours on something, or 3 hours, or 4.75 hours. In each of these examples if I take the input and divide by .25, there is no remainder. that is, .25 / .25 = 1 3 / .25 = 12 4.75 / .25 = 19.
Given the requirement for this application, the following input would NOT be allowed: .6 4.2 5.002 In each of these cases, if I divide by .25 the result would not be even  there would be a remainder, and that's what I'm trying to detect in VBA code. Strangely, when I do something like this RETURN_VAL = 4.2 MOD .25 , I get a divide by zero error.
Does anybody know how to meet my requirement so I can detect an input value from a form that is not divisible by .25 ?
Answers

Unlike the worksheet function MOD in Excel, the Mod operator in VBA works with whole numbers only. Both operands are converted to whole numbers before the remainder is computed.
You could check whether
Int(4 * [TimeWorked]) = 4 * [TimeWorked]
where TimeWorked is the time in decimal hours.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff, Moderator Wednesday, March 7, 2018 5:32 AM
 Marked as answer by Rich 30005 Wednesday, March 7, 2018 1:42 PM
All replies

Unlike the worksheet function MOD in Excel, the Mod operator in VBA works with whole numbers only. Both operands are converted to whole numbers before the remainder is computed.
You could check whether
Int(4 * [TimeWorked]) = 4 * [TimeWorked]
where TimeWorked is the time in decimal hours.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff, Moderator Wednesday, March 7, 2018 5:32 AM
 Marked as answer by Rich 30005 Wednesday, March 7, 2018 1:42 PM
