validation rule?

[acepayne]

Hello,

I have a text box on a form, which is bound to a number field in a table (type: single, 2 decimal places).

I only want to allow values between -20.00 and 20.00. This part is no problem for me. However, I would also like to limit the choices to be multiples of 0.25. (ex.: 0.25, 0.50, 0.75, etc., etc.)

What is the best way to validate for this?

Thanks in advance,
Ace

 

[Jack Cowley]

Code similar to this in the Before Update event should do what you want"

Dim rt As Integer
rt = Right(FieldName, 2)
Select Case rt
Case 25, 50, 75
Exit Sub
Case Else
MsgBox "Must be .25, .50, .75"
Me.Undo
Cancel = True
End Select

 

[acepayne]

Hey Jack,

Thanks for the speedy reply! This works fine, except for whole number values, like 2.00, 3.00, etc. I guess I didn't include that in my example.

I've set the textbox format property to "fixed" with 2 decimal places, and this works fine, but only once it has lost focus.

So, if I enter a 2, or even 2.00, it should be OK as well as the other possible choices (.25, .5, .75)

I tried adding 00 to the Case Statement, but that didn't seem to work.

Further Ideas?

Thanks again,
Gord

 

[Jack Cowley]

See if this does it for you:

Dim rt As Variant
rt = Format(Text8, "00.00")
rt = Right(rt, 2)
Select Case rt
Case 0, 25, 50, 75
Exit Sub
Case Else
MsgBox "wrong"
Me.Undo
Cancel = True
End Select

 

[acepayne]

Jack,

Works great! Thanks so much!!!

Cheers,
Ace