Validation rule to restrict input of one field based on other field in same table

[jobrien4]

I have begun desiging a database for my company that stores information about parts we produce for our customers. I am relatively new to Access and am using Access 2010.

Sometimes customers parts have dimensions given in inches, while other times they are given in millimeters. I would rather have a query do the conversion to inches than the person doing it prior to inputting data. From there, all other calculations I do will be based off of the dimensions in inches.

Here is how I was planning on handling this:

I have a table with the following fields:
Part Number
Thickness(In)
Width(In)
Length(In)
Thickness(MM)
Width(MM)
Length(MM)

When the query is ran, I use an Iif to see whether MM was inputted and if the user inputted in MM, it creates a new field that converts to inches.

However, here is where I need help. I would like a validation rule to only allow Thickness(In) or Thickness(MM) to be inputted. For instance, if the user inputs a value for Thickness(In), I would like Access to prevent the user from inputting Thickness(MM) (unless Thickness(In) is null or =0).

How can I accomplish this?

 

[KenHigg]

I think I would have one fld for qty and one for unit, not two qty's.

 

[The_Doc_Man]

First, metric and inches are both going to require numeric inputs. You CAN'T validate them this way at all.

Second, I would do something like this:

PartsList:
Part Number
Metric - Y/N
Thickness
Width
Length

Third, for this kind of validation, I would ALWAYS AND ONLY use a form for which you can write event code for OnChange or LostFocus so that you can check a value after-the-fact.

Fourth, if you want to make this dynamic off of a single part number, then you need to learn how to do line parsing so that someone could enter "5 in" or "5 mm" and you don't store ANYTHING until after you have parsed the input field. However, this bothers me because having one part number with two different ways of measuring at least seems to be an underdesigned table.