Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by JheyDC, Jul 28, 2006.

  1. JheyDC

    JheyDC
    Expand Collapse
    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.
     
  2. Loading...


  3. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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


    "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    news:778940D9-94B4-43FD-B8CE-688198CF680B@microsoft.com...
    > 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.
     
  4. JheyDC

    JheyDC
    Expand Collapse
    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?

    "Graham Mandeno" wrote:

    > 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
    >
    >
    > "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > news:778940D9-94B4-43FD-B8CE-688198CF680B@microsoft.com...
    > > 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.

    >
    >
    >
     
  5. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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

    "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    news:6D15B7A2-9EC3-4508-BAAC-69DE0486ACFC@microsoft.com...
    >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?
    >
    > "Graham Mandeno" wrote:
    >
    >> 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
    >>
    >>
    >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    >> news:778940D9-94B4-43FD-B8CE-688198CF680B@microsoft.com...
    >> > 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.

    >>
    >>
    >>
     
  6. JheyDC

    JheyDC
    Expand Collapse
    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.

    "Graham Mandeno" wrote:

    > 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
    >
    > "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > news:6D15B7A2-9EC3-4508-BAAC-69DE0486ACFC@microsoft.com...
    > >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?
    > >
    > > "Graham Mandeno" wrote:
    > >
    > >> 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
    > >>
    > >>
    > >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > >> news:778940D9-94B4-43FD-B8CE-688198CF680B@microsoft.com...
    > >> > 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.
    > >>
    > >>
    > >>

    >
    >
    >
     
  7. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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


    "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    news:89632529-CA19-4AD5-9243-CCDE4541561B@microsoft.com...
    > 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.
    >
    > "Graham Mandeno" wrote:
    >
    >> 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
    >>
    >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    >> news:6D15B7A2-9EC3-4508-BAAC-69DE0486ACFC@microsoft.com...
    >> >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?
    >> >
    >> > "Graham Mandeno" wrote:
    >> >
    >> >> 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
    >> >>
    >> >>
    >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    >> >> news:778940D9-94B4-43FD-B8CE-688198CF680B@microsoft.com...
    >> >> > 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.
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  8. JheyDC

    JheyDC
    Expand Collapse
    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.

    "Graham Mandeno" wrote:

    > 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
    >
    >
    > "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > news:89632529-CA19-4AD5-9243-CCDE4541561B@microsoft.com...
    > > 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.
    > >
    > > "Graham Mandeno" wrote:
    > >
    > >> 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
    > >>
    > >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > >> news:6D15B7A2-9EC3-4508-BAAC-69DE0486ACFC@microsoft.com...
    > >> >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?
    > >> >
    > >> > "Graham Mandeno" wrote:
    > >> >
    > >> >> 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
    > >> >>
    > >> >>
    > >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > >> >> news:778940D9-94B4-43FD-B8CE-688198CF680B@microsoft.com...
    > >> >> > 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.
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >
     
  9. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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

    "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    news:53939ED1-A9DA-4A53-8CC4-F219DF743C53@microsoft.com...
    > 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.
    >
    > "Graham Mandeno" wrote:
    >
    >> 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
    >>
    >>
    >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    >> news:89632529-CA19-4AD5-9243-CCDE4541561B@microsoft.com...
    >> > 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.
    >> >
    >> > "Graham Mandeno" wrote:
    >> >
    >> >> 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
    >> >>
    >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    >> >> news:6D15B7A2-9EC3-4508-BAAC-69DE0486ACFC@microsoft.com...
    >> >> >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?
    >> >> >
    >> >> > "Graham Mandeno" wrote:
    >> >> >
    >> >> >> 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
    >> >> >>
    >> >> >>
    >> >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    >> >> >> news:778940D9-94B4-43FD-B8CE-688198CF680B@microsoft.com...
    >> >> >> > 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.
    >> >> >>
    >> >> >>
    >> >> >>
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  10. JheyDC

    JheyDC
    Expand Collapse
    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.


    "Graham Mandeno" wrote:

    > 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
    >
    > "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > news:53939ED1-A9DA-4A53-8CC4-F219DF743C53@microsoft.com...
    > > 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.
    > >
    > > "Graham Mandeno" wrote:
    > >
    > >> 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
    > >>
    > >>
    > >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > >> news:89632529-CA19-4AD5-9243-CCDE4541561B@microsoft.com...
    > >> > 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.
    > >> >
    > >> > "Graham Mandeno" wrote:
    > >> >
    > >> >> 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
    > >> >>
    > >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > >> >> news:6D15B7A2-9EC3-4508-BAAC-69DE0486ACFC@microsoft.com...
    > >> >> >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?
    > >> >> >
    > >> >> > "Graham Mandeno" wrote:
    > >> >> >
    > >> >> >> 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
    > >> >> >>
    > >> >> >>
    > >> >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > >> >> >> news:778940D9-94B4-43FD-B8CE-688198CF680B@microsoft.com...
    > >> >> >> > 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.
    > >> >> >>
    > >> >> >>
    > >> >> >>
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >
     
  11. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    JheyDC wrote:
    > 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?

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  12. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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

    "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    news:165AF356-5F74-40F1-8382-B69A3FDA961D@microsoft.com...
    > 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.
    >
    >
    > "Graham Mandeno" wrote:
    >
    >> 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
    >>
    >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    >> news:53939ED1-A9DA-4A53-8CC4-F219DF743C53@microsoft.com...
    >> > 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.
    >> >
    >> > "Graham Mandeno" wrote:
    >> >
    >> >> 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
    >> >>
    >> >>
    >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    >> >> news:89632529-CA19-4AD5-9243-CCDE4541561B@microsoft.com...
    >> >> > 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.
    >> >> >
    >> >> > "Graham Mandeno" wrote:
    >> >> >
    >> >> >> 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
    >> >> >>
    >> >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    >> >> >> news:6D15B7A2-9EC3-4508-BAAC-69DE0486ACFC@microsoft.com...
    >> >> >> >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?
    >> >> >> >
    >> >> >> > "Graham Mandeno" wrote:
    >> >> >> >
    >> >> >> >> 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
    >> >> >> >>
    >> >> >> >>
    >> >> >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    >> >> >> >> news:778940D9-94B4-43FD-B8CE-688198CF680B@microsoft.com...
    >> >> >> >> > 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.
    >> >> >> >>
    >> >> >> >>
    >> >> >> >>
    >> >> >>
    >> >> >>
    >> >> >>
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  13. JheyDC

    JheyDC
    Expand Collapse
    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!

    "Graham Mandeno" wrote:

    > 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
    >
    > "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > news:165AF356-5F74-40F1-8382-B69A3FDA961D@microsoft.com...
    > > 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.
    > >
    > >
    > > "Graham Mandeno" wrote:
    > >
    > >> 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
    > >>
    > >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > >> news:53939ED1-A9DA-4A53-8CC4-F219DF743C53@microsoft.com...
    > >> > 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.
    > >> >
    > >> > "Graham Mandeno" wrote:
    > >> >
    > >> >> 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
    > >> >>
    > >> >>
    > >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > >> >> news:89632529-CA19-4AD5-9243-CCDE4541561B@microsoft.com...
    > >> >> > 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.
    > >> >> >
    > >> >> > "Graham Mandeno" wrote:
    > >> >> >
    > >> >> >> 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
    > >> >> >>
    > >> >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > >> >> >> news:6D15B7A2-9EC3-4508-BAAC-69DE0486ACFC@microsoft.com...
    > >> >> >> >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?
    > >> >> >> >
    > >> >> >> > "Graham Mandeno" wrote:
    > >> >> >> >
    > >> >> >> >> 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
    > >> >> >> >>
    > >> >> >> >>
    > >> >> >> >> "JheyDC" <JheyDC@discussions.microsoft.com> wrote in message
    > >> >> >> >> news:778940D9-94B4-43FD-B8CE-688198CF680B@microsoft.com...
    > >> >> >> >> > 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.
    > >> >> >> >>
    > >> >> >> >>
    > >> >> >> >>
    > >> >> >>
    > >> >> >>
    > >> >> >>
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >
     

Share This Page