correct syntax for MOD in the validation rule

[Andy Quintana]

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.

 

[Allen Browne]

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.

 

[Tim Ferguson]

I am trying to set
up a numbering system where the last digit is a check digit using MOD 7
validation.

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

 

[david epsom dot com dot au]

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)

 

[Tim Ferguson]

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.

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

 

[david epsom dot com dot au]

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)

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.

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

 

[Tim Ferguson]

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.

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