How do I set up the MOD expression as a validation rule?

[Guest]

I'm trying to input MOD 7 as a validation rule but I keep getting an error
message.

I would appreciate any help I get.

Thank you.

 

[Graham Mandeno]

You asked this question two days ago and it was answered within minutes by
Allen Browne, who said:
Mod is an operator, so it should be possible to use a
validation rule like this in MyField:
[MyField] MOD 7 = 0

This will allow only numbers which are divisible by 7, which seems to be
what you are requiring. Did his answer not suit you? If not, then please
explain why not, and try to phrase your question differently so that we know
what you really DO require.

 

[Guest]

I apologize for asking the same question, was not sure if I submitted
correctly.

Thanks for the quick response.

I did input [MyField] MOD 7 = 0 as a validation rule but when I go ahead and
input the digits on to the field it won't allow it.

The error message I keep getting is " one or more values are prohibited by
the validation rule [MyField] MOD 7 = 0. Enter a value that the expression
for this field can accept". I understand this error message but what I don't
understand is if I am inputting the correct digits why is it still blocking
my inputs?

You asked this question two days ago and it was answered within minutes by
Allen Browne, who said:
Mod is an operator, so it should be possible to use a
validation rule like this in MyField:
[MyField] MOD 7 = 0

This will allow only numbers which are divisible by 7, which seems to be
what you are requiring. Did his answer not suit you? If not, then please
explain why not, and try to phrase your question differently so that we know
what you really DO require.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to input MOD 7 as a validation rule but I keep getting an error
message.

I would appreciate any help I get.

Thank you.

 

[Graham Mandeno]

I presume you realise that [MyField] should be substituted by the actual
name of your field?

What values do you wish to allow, and what do you wish to block?

What are the "correct digits" that you are typing in that are being blocked?

This IS a numeric field, isn't it?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

I apologize for asking the same question, was not sure if I submitted
correctly.

Thanks for the quick response.

I did input [MyField] MOD 7 = 0 as a validation rule but when I go ahead
and
input the digits on to the field it won't allow it.

The error message I keep getting is " one or more values are prohibited by
the validation rule [MyField] MOD 7 = 0. Enter a value that the
expression
for this field can accept". I understand this error message but what I
don't
understand is if I am inputting the correct digits why is it still
blocking
my inputs?

You asked this question two days ago and it was answered within minutes
by
Allen Browne, who said:
Mod is an operator, so it should be possible to use a
validation rule like this in MyField:
[MyField] MOD 7 = 0

This will allow only numbers which are divisible by 7, which seems to be
what you are requiring. Did his answer not suit you? If not, then please
explain why not, and try to phrase your question differently so that we
know
what you really DO require.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to input MOD 7 as a validation rule but I keep getting an
error
message.

I would appreciate any help I get.

Thank you.

 

[Guest]

Yes the field has its actual name.

I am setting up the MOD 7 function so that mistakes can be caught in the
beginning when inputting the digits.

I want to make sure that the last 8th digit that is inputted into that field
is the correct one because there would be times when I would be searching for
a set of numbers and I can't seem to find it because it was typed wrong.

Yes it is a numberic field.

I appreciate your time and effort.

I presume you realise that [MyField] should be substituted by the actual
name of your field?

What values do you wish to allow, and what do you wish to block?

What are the "correct digits" that you are typing in that are being blocked?

This IS a numeric field, isn't it?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

I apologize for asking the same question, was not sure if I submitted
correctly.

Thanks for the quick response.

I did input [MyField] MOD 7 = 0 as a validation rule but when I go ahead
and
input the digits on to the field it won't allow it.

The error message I keep getting is " one or more values are prohibited by
the validation rule [MyField] MOD 7 = 0. Enter a value that the
expression
for this field can accept". I understand this error message but what I
don't
understand is if I am inputting the correct digits why is it still
blocking
my inputs?

You asked this question two days ago and it was answered within minutes
by
Allen Browne, who said:
Mod is an operator, so it should be possible to use a
validation rule like this in MyField:
[MyField] MOD 7 = 0

This will allow only numbers which are divisible by 7, which seems to be
what you are requiring. Did his answer not suit you? If not, then please
explain why not, and try to phrase your question differently so that we
know
what you really DO require.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


I'm trying to input MOD 7 as a validation rule but I keep getting an
error
message.

I would appreciate any help I get.

Thank you.

 

[Graham Mandeno]

But you have not answered this question:
The expression you have will disallow any number that is not evenly
divisible by 7. If that is NOT what you want, then please say what you DO
want.

I have a suspicion that your understanding of MOD 7 is different from VBA's.
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Yes the field has its actual name.

I am setting up the MOD 7 function so that mistakes can be caught in the
beginning when inputting the digits.

I want to make sure that the last 8th digit that is inputted into that
field
is the correct one because there would be times when I would be searching
for
a set of numbers and I can't seem to find it because it was typed wrong.

Yes it is a numberic field.

I appreciate your time and effort.

I presume you realise that [MyField] should be substituted by the actual
name of your field?

What values do you wish to allow, and what do you wish to block?

What are the "correct digits" that you are typing in that are being
blocked?

This IS a numeric field, isn't it?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

I apologize for asking the same question, was not sure if I submitted
correctly.

Thanks for the quick response.

I did input [MyField] MOD 7 = 0 as a validation rule but when I go
ahead
and
input the digits on to the field it won't allow it.

The error message I keep getting is " one or more values are prohibited
by
the validation rule [MyField] MOD 7 = 0. Enter a value that the
expression
for this field can accept". I understand this error message but what I
don't
understand is if I am inputting the correct digits why is it still
blocking
my inputs?

:

You asked this question two days ago and it was answered within
minutes
by
Allen Browne, who said:
Mod is an operator, so it should be possible to use a
validation rule like this in MyField:
[MyField] MOD 7 = 0

This will allow only numbers which are divisible by 7, which seems to
be
what you are requiring. Did his answer not suit you? If not, then
please
explain why not, and try to phrase your question differently so that
we
know
what you really DO require.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


I'm trying to input MOD 7 as a validation rule but I keep getting an
error
message.

I would appreciate any help I get.

Thank you.

 

[Guest]

What I'm actually doing is inputting 8 digits onto the field.

What I want Access to do is to block numbers that are "not evenly

divisible by 7".

There are times that the validation works, but sometimes it gives me the
error message. I get the message a lot even when I try it out with correct
digits from previous records.

I'm using the database for work related programs so that my staff can
retrieve these numbers and match them. But in order to do so, I need to set
up this rule so that the numbers will be correct when retrieved.

But you have not answered this question:
The expression you have will disallow any number that is not evenly
divisible by 7. If that is NOT what you want, then please say what you DO
want.

I have a suspicion that your understanding of MOD 7 is different from VBA's.
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Yes the field has its actual name.

I am setting up the MOD 7 function so that mistakes can be caught in the
beginning when inputting the digits.

I want to make sure that the last 8th digit that is inputted into that
field
is the correct one because there would be times when I would be searching
for
a set of numbers and I can't seem to find it because it was typed wrong.

Yes it is a numberic field.

I appreciate your time and effort.

I presume you realise that [MyField] should be substituted by the actual
name of your field?

What values do you wish to allow, and what do you wish to block?

What are the "correct digits" that you are typing in that are being
blocked?

This IS a numeric field, isn't it?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

I apologize for asking the same question, was not sure if I submitted
correctly.

Thanks for the quick response.

I did input [MyField] MOD 7 = 0 as a validation rule but when I go
ahead
and
input the digits on to the field it won't allow it.

The error message I keep getting is " one or more values are prohibited
by
the validation rule [MyField] MOD 7 = 0. Enter a value that the
expression
for this field can accept". I understand this error message but what I
don't
understand is if I am inputting the correct digits why is it still
blocking
my inputs?

:

You asked this question two days ago and it was answered within
minutes
by
Allen Browne, who said:
Mod is an operator, so it should be possible to use a
validation rule like this in MyField:
[MyField] MOD 7 = 0

This will allow only numbers which are divisible by 7, which seems to
be
what you are requiring. Did his answer not suit you? If not, then
please
explain why not, and try to phrase your question differently so that
we
know
what you really DO require.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


I'm trying to input MOD 7 as a validation rule but I keep getting an
error
message.

I would appreciate any help I get.

Thank you.

 

[Graham Mandeno]

So, if you enter this 8-digit number:
86419746
does it allow it or not?

Would you expect it to allow it?

Can you give me an example of a number you would expect it be allowed, but
it gets blocked?

Can you give me an example of a number you would expect it be blocked, but
it is allowed?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

What I'm actually doing is inputting 8 digits onto the field.

What I want Access to do is to block numbers that are "not evenly

divisible by 7".

There are times that the validation works, but sometimes it gives me the
error message. I get the message a lot even when I try it out with
correct
digits from previous records.

I'm using the database for work related programs so that my staff can
retrieve these numbers and match them. But in order to do so, I need to
set
up this rule so that the numbers will be correct when retrieved.

But you have not answered this question:

What values do you wish to allow, and what do you wish to block?

The expression you have will disallow any number that is not evenly
divisible by 7. If that is NOT what you want, then please say what you
DO
want.

I have a suspicion that your understanding of MOD 7 is different from
VBA's.
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Yes the field has its actual name.

I am setting up the MOD 7 function so that mistakes can be caught in
the
beginning when inputting the digits.

I want to make sure that the last 8th digit that is inputted into that
field
is the correct one because there would be times when I would be
searching
for
a set of numbers and I can't seem to find it because it was typed
wrong.

Yes it is a numberic field.

I appreciate your time and effort.

:

I presume you realise that [MyField] should be substituted by the
actual
name of your field?

What values do you wish to allow, and what do you wish to block?

What are the "correct digits" that you are typing in that are being
blocked?

This IS a numeric field, isn't it?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

I apologize for asking the same question, was not sure if I submitted
correctly.

Thanks for the quick response.

I did input [MyField] MOD 7 = 0 as a validation rule but when I go
ahead
and
input the digits on to the field it won't allow it.

The error message I keep getting is " one or more values are
prohibited
by
the validation rule [MyField] MOD 7 = 0. Enter a value that the
expression
for this field can accept". I understand this error message but
what I
don't
understand is if I am inputting the correct digits why is it still
blocking
my inputs?

:

You asked this question two days ago and it was answered within
minutes
by
Allen Browne, who said:
Mod is an operator, so it should be possible to use a
validation rule like this in MyField:
[MyField] MOD 7 = 0

This will allow only numbers which are divisible by 7, which seems
to
be
what you are requiring. Did his answer not suit you? If not, then
please
explain why not, and try to phrase your question differently so
that
we
know
what you really DO require.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


I'm trying to input MOD 7 as a validation rule but I keep getting
an
error
message.

I would appreciate any help I get.

Thank you.

 

[Guest]

86419746 is allowed. Its weird because the last digit should be 5 and not six.

An example that would be allowed is 94882550.
An example that would not be allowed is 96762212.

Thanks again.

So, if you enter this 8-digit number:
86419746
does it allow it or not?

Would you expect it to allow it?

Can you give me an example of a number you would expect it be allowed, but
it gets blocked?

Can you give me an example of a number you would expect it be blocked, but
it is allowed?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

What I'm actually doing is inputting 8 digits onto the field.

What I want Access to do is to block numbers that are "not evenly

divisible by 7".

There are times that the validation works, but sometimes it gives me the
error message. I get the message a lot even when I try it out with
correct
digits from previous records.

I'm using the database for work related programs so that my staff can
retrieve these numbers and match them. But in order to do so, I need to
set
up this rule so that the numbers will be correct when retrieved.

But you have not answered this question:
What values do you wish to allow, and what do you wish to block?

The expression you have will disallow any number that is not evenly
divisible by 7. If that is NOT what you want, then please say what you
DO
want.

I have a suspicion that your understanding of MOD 7 is different from
VBA's.
--

Graham Mandeno [Access MVP]
Auckland, New Zealand


Yes the field has its actual name.

I am setting up the MOD 7 function so that mistakes can be caught in
the
beginning when inputting the digits.

I want to make sure that the last 8th digit that is inputted into that
field
is the correct one because there would be times when I would be
searching
for
a set of numbers and I can't seem to find it because it was typed
wrong.

Yes it is a numberic field.

I appreciate your time and effort.

:

I presume you realise that [MyField] should be substituted by the
actual
name of your field?

What values do you wish to allow, and what do you wish to block?

What are the "correct digits" that you are typing in that are being
blocked?

This IS a numeric field, isn't it?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

I apologize for asking the same question, was not sure if I submitted
correctly.

Thanks for the quick response.

I did input [MyField] MOD 7 = 0 as a validation rule but when I go
ahead
and
input the digits on to the field it won't allow it.

The error message I keep getting is " one or more values are
prohibited
by
the validation rule [MyField] MOD 7 = 0. Enter a value that the
expression
for this field can accept". I understand this error message but
what I
don't
understand is if I am inputting the correct digits why is it still
blocking
my inputs?

:

You asked this question two days ago and it was answered within
minutes
by
Allen Browne, who said:
Mod is an operator, so it should be possible to use a
validation rule like this in MyField:
[MyField] MOD 7 = 0

This will allow only numbers which are divisible by 7, which seems
to
be
what you are requiring. Did his answer not suit you? If not, then
please
explain why not, and try to phrase your question differently so
that
we
know
what you really DO require.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


I'm trying to input MOD 7 as a validation rule but I keep getting
an
error
message.

I would appreciate any help I get.

Thank you.

 

[Rick Brandt]

86419746 is allowed. Its weird because the last digit should be 5
and not six.

An example that would be allowed is 94882550.
An example that would not be allowed is 96762212.

If you type...

?86419746 Mod 7 <enter>

....into the immediate window you will see that it DOES return zero.
86419746 divided by 7 is exactly 12345678 with no remainder which is what
getting the zero means (no remainder).

How are you expecting Mod to work?

 

[Graham Mandeno]

Ah....... I think the light has just switched on.

You want to validate a MOD7 *check digit* ????????

So, if I understand you correctly, you want to take off the last digit and
divide the rest of the number by 7. The result should equal the last digit.

Try this:
([MyField]\10) Mod 7=[MyField] Mod 10

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

86419746 is allowed. Its weird because the last digit should be 5 and not
six.

An example that would be allowed is 94882550.
An example that would not be allowed is 96762212.

Thanks again.

So, if you enter this 8-digit number:
86419746
does it allow it or not?

Would you expect it to allow it?

Can you give me an example of a number you would expect it be allowed,
but
it gets blocked?

Can you give me an example of a number you would expect it be blocked,
but
it is allowed?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

What I'm actually doing is inputting 8 digits onto the field.

What I want Access to do is to block numbers that are "not evenly
divisible by 7".

There are times that the validation works, but sometimes it gives me
the
error message. I get the message a lot even when I try it out with
correct
digits from previous records.

I'm using the database for work related programs so that my staff can
retrieve these numbers and match them. But in order to do so, I need
to
set
up this rule so that the numbers will be correct when retrieved.

:

But you have not answered this question:
What values do you wish to allow, and what do you wish to block?

The expression you have will disallow any number that is not evenly
divisible by 7. If that is NOT what you want, then please say what
you
DO
want.

I have a suspicion that your understanding of MOD 7 is different from
VBA's.
--

Graham Mandeno [Access MVP]
Auckland, New Zealand


Yes the field has its actual name.

I am setting up the MOD 7 function so that mistakes can be caught in
the
beginning when inputting the digits.

I want to make sure that the last 8th digit that is inputted into
that
field
is the correct one because there would be times when I would be
searching
for
a set of numbers and I can't seem to find it because it was typed
wrong.

Yes it is a numberic field.

I appreciate your time and effort.

:

I presume you realise that [MyField] should be substituted by the
actual
name of your field?

What values do you wish to allow, and what do you wish to block?

What are the "correct digits" that you are typing in that are being
blocked?

This IS a numeric field, isn't it?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

I apologize for asking the same question, was not sure if I
submitted
correctly.

Thanks for the quick response.

I did input [MyField] MOD 7 = 0 as a validation rule but when I
go
ahead
and
input the digits on to the field it won't allow it.

The error message I keep getting is " one or more values are
prohibited
by
the validation rule [MyField] MOD 7 = 0. Enter a value that the
expression
for this field can accept". I understand this error message but
what I
don't
understand is if I am inputting the correct digits why is it
still
blocking
my inputs?

:

You asked this question two days ago and it was answered within
minutes
by
Allen Browne, who said:
Mod is an operator, so it should be possible to use a
validation rule like this in MyField:
[MyField] MOD 7 = 0

This will allow only numbers which are divisible by 7, which
seems
to
be
what you are requiring. Did his answer not suit you? If not,
then
please
explain why not, and try to phrase your question differently so
that
we
know
what you really DO require.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


I'm trying to input MOD 7 as a validation rule but I keep
getting
an
error
message.

I would appreciate any help I get.

Thank you.

 

[Guest]

Yes!!!

It works. You knew exactly what I wanted to do. Thanks a lot Graham. You
really know your stuff.

Until next time?! : )

Have a great weekend!

Ah....... I think the light has just switched on.

You want to validate a MOD7 *check digit* ????????

So, if I understand you correctly, you want to take off the last digit and
divide the rest of the number by 7. The result should equal the last digit.

Try this:
([MyField]\10) Mod 7=[MyField] Mod 10

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

86419746 is allowed. Its weird because the last digit should be 5 and not
six.

An example that would be allowed is 94882550.
An example that would not be allowed is 96762212.

Thanks again.

So, if you enter this 8-digit number:
86419746
does it allow it or not?

Would you expect it to allow it?

Can you give me an example of a number you would expect it be allowed,
but
it gets blocked?

Can you give me an example of a number you would expect it be blocked,
but
it is allowed?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

What I'm actually doing is inputting 8 digits onto the field.

What I want Access to do is to block numbers that are "not evenly
divisible by 7".

There are times that the validation works, but sometimes it gives me
the
error message. I get the message a lot even when I try it out with
correct
digits from previous records.

I'm using the database for work related programs so that my staff can
retrieve these numbers and match them. But in order to do so, I need
to
set
up this rule so that the numbers will be correct when retrieved.

:

But you have not answered this question:
What values do you wish to allow, and what do you wish to block?

The expression you have will disallow any number that is not evenly
divisible by 7. If that is NOT what you want, then please say what
you
DO
want.

I have a suspicion that your understanding of MOD 7 is different from
VBA's.
--

Graham Mandeno [Access MVP]
Auckland, New Zealand


Yes the field has its actual name.

I am setting up the MOD 7 function so that mistakes can be caught in
the
beginning when inputting the digits.

I want to make sure that the last 8th digit that is inputted into
that
field
is the correct one because there would be times when I would be
searching
for
a set of numbers and I can't seem to find it because it was typed
wrong.

Yes it is a numberic field.

I appreciate your time and effort.

:

I presume you realise that [MyField] should be substituted by the
actual
name of your field?

What values do you wish to allow, and what do you wish to block?

What are the "correct digits" that you are typing in that are being
blocked?

This IS a numeric field, isn't it?
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

I apologize for asking the same question, was not sure if I
submitted
correctly.

Thanks for the quick response.

I did input [MyField] MOD 7 = 0 as a validation rule but when I
go
ahead
and
input the digits on to the field it won't allow it.

The error message I keep getting is " one or more values are
prohibited
by
the validation rule [MyField] MOD 7 = 0. Enter a value that the
expression
for this field can accept". I understand this error message but
what I
don't
understand is if I am inputting the correct digits why is it
still
blocking
my inputs?

:

You asked this question two days ago and it was answered within
minutes
by
Allen Browne, who said:
Mod is an operator, so it should be possible to use a
validation rule like this in MyField:
[MyField] MOD 7 = 0

This will allow only numbers which are divisible by 7, which
seems
to
be
what you are requiring. Did his answer not suit you? If not,
then
please
explain why not, and try to phrase your question differently so
that
we
know
what you really DO require.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


I'm trying to input MOD 7 as a validation rule but I keep
getting
an
error
message.

I would appreciate any help I get.

Thank you.