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

[Joey P]

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

 

[rkc]

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'

 

[Bob Quintal]

(e-mail address removed) (Joey P) wrote in

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