correct syntax for MOD in the validation rule

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What is the proper syntax for MOD in the validation rule? I am trying to set
up a numbering system where the last digit is a check digit using MOD 7
validation.
 
Andy, I suspect this will be too complex to use in the Validation Rule of
the field in the table.

Instead use the BeforeUpdate event of the control (or of the Form) where the
entry is made. In the event procedure, you can test for null, parse the
right-most digit (MOD 10?) from the number, and perform the calcuation. Or
you can call a user-defined-function to do all that if you want to re-use it
in other places.
 
I am trying to set
up a numbering system where the last digit is a check digit using MOD 7
validation.
Click to expand...

Not tested, but it should be something like this:

clng(left(myfield, len(myfield)-1)) mod 7 = cint(right(MyField,1)
or is null


Hope it helps


Tim F
 
That's interesting. I don't think you can do that. No particular
reason, it just looks like it doesn't work. I see that it doesn't
work (that way) in the query designer either: you can write it
into the sql, or include a specific reference to the field, but
Access just doesn't seem to recognise it as an operator when
used as a criteria.

(david)
 
That's interesting. I don't think you can do that. No particular
reason, it just looks like it doesn't work. I see that it doesn't
work (that way) in the query designer either: you can write it
into the sql, or include a specific reference to the field, but
Access just doesn't seem to recognise it as an operator when
used as a criteria.
Click to expand...

This works fine in my test table (copied straight from the table design
window):-

Is Null Or (([age] Mod 2)=0)

and does throw an error if you try to enter an odd number.

For what it's worth, this one works too if the field is an integer:

([CRCCheck] is null) or
((([CRCCheck] \ 10) mod 7) = (CRCCheck Mod 10))


Hope that helps


Tim F
 
I didn't know that you could reference the field
name in the column validation property: I thought
you had to use DDL to add that kind of validation.

(david)


Tim Ferguson said:
That's interesting. I don't think you can do that. No particular
reason, it just looks like it doesn't work. I see that it doesn't
work (that way) in the query designer either: you can write it
into the sql, or include a specific reference to the field, but
Access just doesn't seem to recognise it as an operator when
used as a criteria.
Click to expand...

This works fine in my test table (copied straight from the table design
window):-

Is Null Or (([age] Mod 2)=0)

and does throw an error if you try to enter an odd number.

For what it's worth, this one works too if the field is an integer:

([CRCCheck] is null) or
((([CRCCheck] \ 10) mod 7) = (CRCCheck Mod 10))


Hope that helps


Tim F
Click to expand...
 
I didn't know that you could reference the field
name in the column validation property: I thought
you had to use DDL to add that kind of validation.
Click to expand...

You _can_ leave out the field name if it's a simple function like "Is
Null" or "< 100" etc. There is no harm in specifying it "MyField Is Null Or
MyField < 100" and so on, and sometimes you can't do it sensibly without.

Using DDL you would have to use a properly formatted SQL expression. In the
GUI, access is kind enough to take a half-formed bit of an expression and
tidy it up behind the scenes.

All the best

Tim F
 
Back
Top