Referencing table data in field validation rule

[toad848]

Hello,

I am using Access XP (2002) SP3. I've created a table that is to store the details of certain shipments. Some shipments are measured in terms of pieces shipped, whereas in other cases the shipment is measured in kilograms. This being the case, I have one field called 'MeasuremenUnit' that contains the measurement unit (i.e. pieces or kilograms) and another called 'Quantiy' that contains the number of units shipped. I want a validation rule for 'Quantity' that disallows fractional numbers if the field 'MeasurementUnit' for that record contains the value 'pieces'. For this, I believe that I'd need to reference, in the validation rule, the value contained in the MeasumentUnit field of the current record. How is this done?

I'd much appreciate any help.

Thanks!

 

[Rabbie]

It won't work at tabble level so do the validation on the Form where you enter the data.

 

[toad848]

It won't work at tabble level so do the validation on the Form where you enter the data.

Thank you for your prompt response, Rabbie. If I can't restrict the data entered at the table level, I'd want to prevent users from creating/modifying records directly in the tables, and force them to use the forms. How can I do this?

Thanks!

 

[boblarson]

It won't work at tabble level so do the validation on the Form where you enter the data.

Actually, that isn't true. You can create table level validation rules.

1. Go into the table in Design View and don't select a field.

2. Click VIEW > PROPERTIES

3. There is a validation rule available for table level validation.

Not sure what the syntax would be for the rule you need for whole numbers, but if you can figure out an IIF statement for that then you can put it in the table level validation. For this particular problem, it might not be possible to do a validation rule, as such.

 

[Rabbie]

Bob I was under the impression the OP wanted to use a value in another field as part of the Validation Rule. Didn't think you could do that

 

[boblarson]

Bob I was under the impression the OP wanted to use a value in another field as part of the Validation Rule. Didn't think you could do that

Yep, you can using the TABLE level validation (not a field level validation)

 

[toad848]

Thanks, Bob. I was just enlightened on this concept of table validation rules by someone on another forum. I think I should be able to figure out the syntax.

Actually, that isn't true. You can create table level validation rules.

1. Go into the table in Design View and don't select a field.

2. Click VIEW > PROPERTIES

3. There is a validation rule available for table level validation.

Not sure what the syntax would be for the rule you need for whole numbers, but if you can figure out an IIF statement for that then you can put it in the table level validation. For this particular problem, it might not be possible to do a validation rule, as such.