Validation Issue 1

[Artois27]

I want a validation rule on a field that only allows entry of whole numbers or numbers that end with .5. For example 0.5, 1, 1.5, 2, 2.5 etc This number sequence can go on into the 1000s.Also the field can not be Null or less than 0.5. So far I have; Is Null Or >=0.5 but I don't know how to get the above to work. Perhaps a input mask would work better?

 

[SkipVought]

hi,
[tt]
iif([YourField]*10/5 = int(YourField]*10/5),true,false)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Artois27]

Thanks for the reply, when I put your line into the validation rule of the filed (duration) in table properties, it says "Unknown function "if" in the validation expression" when I try and save the table. Any idea?

 

[SkipVought]

"if" IS an unknown function in Access!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Artois27]

Ok so if "if" is an unknown function in Access, why did you post it as a solution to my problem? Sorry if i'm being daft. What should I put in the Validation Rule field property to get this working as I would like? Or should I put you code somewhere ese to get this working?

 

[MajP]

I think the point is you made a typo
iif
not if

 

[SkipVought]

because I did NOT post "if"!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Artois27]

I put the following into the properties for the validation rule, iif([Duration]*10/5=Int("Duration]*10/5"),True,False)

It still comes up with "This expression is typed incorrectly or is too complex" when I try to save the table?

The field name is Duration by the way

 

[SkipVought]

It would actually be better to write the rule
[tt]
[YourField]*10/5 = int(YourField]*10/5)
[/tt]



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MajP]

@Skip,
Did not test, but I believe you have to wrap the field in NZ. If the value is null, both sides would return null
null = null returns false

iif(nz([YourField],0)*10/5 = int(nz([YourField],0)*10/5),true,false)

@Artois

you can also put something like this in the before update

Private Sub Text0_BeforeUpdate(Cancel As Integer)
  If Not isValid(Me.Text0) Then
    Cancel = True
    MsgBox "Value must be null, or evenly divisible by .5)"
    Me.Text0.Undo
  End If
End Sub

Public Function isValid(varVal As Variant) As Boolean
  If IsNull(varVal) Then
    isValid = True
  ElseIf IsNumeric(varVal) Then
    If Int(varVal / 0.5) = varVal / 0.5 Then
      isValid = True
    End If
  End If
End Function

 

[SkipVought]

NO QUOTES in this expression!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

Skip's solution should work but can be written as:
Validation Rule: [Duration]*2=Int([Duration]*2)

Duane
Hook'D on Access
MS Access MVP

 

[Artois27]

Thanks for everyone for your help. dhookom that worked great! I don't get how it works but it works. Simple solution. Thanks

 

[dhookom]

Artois,
The Int() function truncates any decimal part of a number resulting in a whole number (Integer).

Just plug in some numbers to see what happens.

Duane
Hook'D on Access
MS Access MVP