---

MS Access: Creating a custom round function

Question: In Microsoft Access, I have an Overtime form for the employees. My problem is that while calculating the value, I need the following rules to apply:

If the fraction portion of the number is below 0.5, then I want to round the number down.
If the fraction portion is greater than or equal to 0.5, then the fraction should be 0.5.

For example, a value of 6.7 hours should display as 6.5.

Answer: To accomplish this, you need to create a custom function.

You'll need to open your Access database and create a new module.

Then paste into the new module the following function:

Function CustomRound(pValue As Double) As Double

   Dim LWhole As Long
   Dim LFraction As Double

   'Retrieve integer part of the number
   LWhole = Int(pValue)

   'Retrieve the fraction part of the number
   LFraction = pValue - LWhole

   If LFraction < 0.5 Then
      CustomRound = LWhole
   Else
      CustomRound = LWhole + 0.5
   End If

End Function

Now, when you want to round your values, you can use the round function as follows:

CustomRound (6.7)
Result: 6.5

CustomRound (6.5)
Result: 6.5

CustomRound (6.49)
Result: 6.0