Converting MM to Inches (and vice versa) in Query

[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.

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.

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?

[Robeen]

You can use the After Update or LostFocus Event of Thickness(In) or Thickness(MM) and if there is a value > 0 entered in one - then you can Set the Enabled Property of the other one to False.

Eg:
Right-Click ThicknessIN
Click Properties
Click the Event Tab
Click After Update
Click Code Builder
Type in something like this:

 
If Me.ThicknessIN > 0 Then
    Me.ThicknessMM.Enabled = False
End If

I have not tested the above code so you might have to tweak it.
I've put it there to give you somethign to start with.

Let me know if you need further clarification.