Validation rule trouble - ensuring values held in one field are less than those held in another fiel

Discussion in 'Microsoft Access Queries' started by Joey P, Dec 7, 2003.

  1. Joey P

    Joey P Guest

    Hi all,

    I am doing a project for university whereby i have to implement a
    simple database related to a frozen foods company. I am having some
    trouble though creating a validation rule for one of my fields. I
    have a table called "Product" and two of the fields included in this
    table are "Cost Price" and "Retail Price". I need to create a
    validation rule so that the Cost Price is always less than the Retail
    Price. I have tried entering all sorts of validation rules into the
    Cost Price field such as....

    <Retail Price
    and
    <"Retail Price"

    ...... but i just can't seem to figure out the validation rule that
    will enable me to do what i need to do. I'm not allowed to use Visual
    Basic or anything so I'd be really grateful if someone could help me
    to devise a simple validation rule that will solve my problem. Thanks
    for all the help!

    Joey P
     
    Joey P, Dec 7, 2003
    #1
    1. Advertisements

  2. Joey P

    rkc Guest

    "Joey P" <> wrote in message
    news:...
    > Hi all,
    >
    > I am doing a project for university whereby i have to implement a
    > simple database related to a frozen foods company. I am having some
    > trouble though creating a validation rule for one of my fields. I
    > have a table called "Product" and two of the fields included in this
    > table are "Cost Price" and "Retail Price". I need to create a
    > validation rule so that the Cost Price is always less than the Retail
    > Price. I have tried entering all sorts of validation rules into the
    > Cost Price field such as....
    >
    > <Retail Price
    > and
    > <"Retail Price"
    >
    > ..... but i just can't seem to figure out the validation rule that
    > will enable me to do what i need to do. I'm not allowed to use Visual
    > Basic or anything so I'd be really grateful if someone could help me
    > to devise a simple validation rule that will solve my problem. Thanks
    > for all the help!


    You can't reference another field in a field level validation rule.
    You can reference any number of fields in a record level validation rule.
    You need a record level validation rule.

    Type
    'Validate or restrict data entry in tables'
    into the answer wizard in Help.
    Click on
    'Define a validation rule to control when a record can be saved'
     
    rkc, Dec 7, 2003
    #2
    1. Advertisements

  3. Joey P

    Bob Quintal Guest

    (Joey P) wrote in
    news::

    > Hi all,
    >
    > I am doing a project for university whereby i have to implement a
    > simple database related to a frozen foods company. I am having some
    > trouble though creating a validation rule for one of my fields. I
    > have a table called "Product" and two of the fields included in this
    > table are "Cost Price" and "Retail Price". I need to create a
    > validation rule so that the Cost Price is always less than the Retail
    > Price. I have tried entering all sorts of validation rules into the
    > Cost Price field such as....
    >
    > <Retail Price
    > and
    > <"Retail Price"
    >
    > ..... but i just can't seem to figure out the validation rule that
    > will enable me to do what i need to do. I'm not allowed to use Visual
    > Basic or anything so I'd be really grateful if someone could help me
    > to devise a simple validation rule that will solve my problem. Thanks
    > for all the help!
    >
    > Joey P
    >



    Since prices are currency, not strings, your second won't work.
    < retail price is closer, but the space between the l and the p can
    confuse access. Use < [Retail Price], or do the smart thing and rename
    the field Retail_Price. Those pesky spaces can do all sorts of mayhem.

    I once spent 3 days tracking down a problem with somebody's table where
    they had put two (2) spaces between words in a few columns. Whith a small
    proportional font, I just couldn't see the extra ones.

    Good luck.

    Bob Q
     
    Bob Quintal, Dec 7, 2003
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. tt

    how do I group or delete values in one fiel

    tt, Aug 18, 2003, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    150
    Michel Walsh
    Aug 18, 2003
  2. Guest
    Replies:
    1
    Views:
    560
    Guest
    May 4, 2005
  3. Guest

    Great than value/less than other value

    Guest, Jun 27, 2005, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    281
    Guest
    Jun 28, 2005
  4. JohnLute

    greater than / less than

    JohnLute, Jun 11, 2008, in forum: Microsoft Access Queries
    Replies:
    11
    Views:
    301
    John Spencer
    Jun 11, 2008
  5. Tajul
    Replies:
    1
    Views:
    137
    John W. Vinson
    May 10, 2009
Loading...

Share This Page