Data validation to nearest quarter value (1 Viewer)

ECEK · May 25, 2016

I have a number field and I want to validate the data to only be able to end numbers with a decimal value of .25, .5 and .75 only

I can do this in Excel by using MRound

Does anybody know what formula I could put in the fields validation rule?

Tieval · May 25, 2016

In order to round to the nearest quarter multiply by 4, round, and then divide by 4:

Code:

Round(4 * [MyField], 0) / 4

arnelgp · May 25, 2016

Dim d as double
if [field] <> Int([field]) then
d=[field] - int([field])
if d <> 0.25 or d <> 0.5 or d<>0.75 then
' not in decimal range
' show messagebox
end if
end if

Isskint · May 25, 2016

The validation rule would be

Code:

((([cbv]-Int([cbv]))*100)/25)=Int(((([cbv]-Int([cbv]))*100)/25))

where [cbv] is the name of your number field. I am sure this can be simplified with MOD but i can not get MOD to work :banghead:

sneuberg · May 25, 2016

As a function

Code:

Public Function QuarterTest(x As Double) As Boolean

x = x - Int(x)
If x = 0.25 Or x = 0.5 Or x = 0.75 Then
    QuarterTest = True
Else
    QuarterTest = False
End If

End Function

sneuberg · May 25, 2016

Isskint said:
The validation rule would be

Code:

((([cbv]-Int([cbv]))*100)/25)=Int(((([cbv]-Int([cbv]))*100)/25))

where [cbv] is the name of your number field. I am sure this can be simplified with MOD but i can not get MOD to work :banghead:

Returns true for whole numbers, e.g., 1.00. OP said only ending in 0.25, 0.5 and 0.75

Isskint · May 25, 2016

sneuberg said:
Returns true for whole numbers, e.g., 1.00. OP said only ending in 0.25, 0.5 and 0.75

Good spot sneuberg,
So add an AND to check for whole numbers, like..

Code:

((([cbv]-Int([cbv]))*100)/25)=Int(((([cbv]-Int([cbv]))*100)/25)) And (Int([cbv])<>[cbv])

sneuberg & arnelgp whilst your solutions would provide the answer, I did not think custom functions could be used in validation rules.

sneuberg · May 25, 2016

Isskint said:
sneuberg & arnelgp whilst your solutions would provide the answer, I did not think custom functions could be used in validation rules.

I find the the form validation a nasty thing to work with. We always done our validations in the form's before update. The function could be used there something like:

Code:

Private Sub cbv_BeforeUpdate(Cancel As Integer)

If Not QuarterTest(Me.cbv) Then
    MsgBox "Doesn't end in 0.25 or 0.5 or 0.75"
    Cancel = True
End If

End Sub

jdraw · May 25, 2016

I somehow doubt that is what the OP really wants based on the title of the post.
My interpretation was to return the closest .25 value, but who, except the OP, knows the details.

sneuberg · May 25, 2016

jdraw said:
I somehow doubt that is what the OP really wants based on the title of the post.
My interpretation was to return the closest .25 value, but who, except the OP, knows the details.

I agree and it doesn't seem to be even clear if the OP wants validation or rounding.

jdraw · May 26, 2016

For what it's worth, here is a function that might be applicable. It's sort of a Round to Nearest, but can optionally RoundUp or RoundDown. (tested, but not exhaustively)

It uses this Enum

Code:

Public Enum RoundingDirection
    Nearest  '0
    Up       '1
    Down     '2
End Enum

Code:

'---------------------------------------------------------------------------------------
' Procedure : GetRoundedTo
' Author    : mellon
' Date      : 25/05/2016
' Purpose   : Function was derived from a few search results and upgraded from vb.
'
' To return "The Rounded"   nearest, rounded UP or rounded DOWN value based on inputting a number called mNumber,
' and the Target range and an optional parameter.
'
'Example:  GetRoundedTo(7.51, .50, 1) means Round Up 7.51 to the next .50  ++> returns 8
'          GetRoundedTo(7.51, .50, 2) means Round DOWN to the next .50   ====> returns 7.5
'          GetRoundedTo(7.51, .50, 0) means Round to the NEAREST .50 ===> returns 7.5 (this is the default RoundingDirection)
'---------------------------------------------------------------------------------------
'
 Function GetRoundedTo(mNumber As Double, mTarget As Double, Optional mDirection As RoundingDirection = Nearest) As Double
       Dim nearestValue As Double
10       On Error GoTo GetRoundedTo_Error

20        nearestValue = (CInt(mNumber / mTarget) * mTarget)
30        Select Case mDirection
              Case RoundingDirection.Nearest
40                GetRoundedTo = nearestValue
50            Case RoundingDirection.Up
60                If nearestValue >= mNumber Then
70                   GetRoundedTo = nearestValue
80                Else
90                    GetRoundedTo = nearestValue + mTarget
100               End If
110           Case RoundingDirection.Down
120               If nearestValue <= mNumber Then
130                   GetRoundedTo = nearestValue
140               Else
150                   GetRoundedTo = nearestValue - mTarget
160               End If
170       End Select

180      On Error GoTo 0
190      Exit Function

GetRoundedTo_Error:

200       MsgBox "Error " & Err.number & " in line " & Erl & " (" & Err.Description & ") in procedure GetRoundedTo of Module Module3"
End Function

Sample usage:
?GetRoundedTo(-7.76 ,.25,2 )
-8
?GetRoundedTo(7.76 ,.25,2 )
7.75

Data validation to nearest quarter value (1 Viewer)

ECEK

Registered User.

Tieval

Still Clueless

arnelgp

..forever waiting... waiting for jellybean!

Isskint

Slowly Developing

sneuberg

AWF VIP

sneuberg

AWF VIP

Isskint

Slowly Developing

sneuberg

AWF VIP

jdraw

Super Moderator

sneuberg

AWF VIP

jdraw

Super Moderator

Similar threads

Users who are viewing this thread