I'm trying to populate a field based upon values is 2 other fields. I have a
field DEGREES and I want to add it to the sum of the field MINUTES/60 to
create a new field DECIMAL DEGREES. I keep getting an error "database engine
does not recognize the field 'MINUTES' in a validation expression". I would
like this field to populate when the data in the other fields is entered.
I'm not very proficient with access, so I'm trying to learn as I go. Thanks.
Where are you trying to do this? in a *validation rule*? That wouldn't be the
right place even IF you wanted to do this (which you don't!)
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.
Instead, use a Form to enter data to your table. Table datasheets are of VERY
limited functionality and are not appropriate for data entry or editing. I
would suggest that you have a ANGLE field in your table, of Single or Double
datatype so it can accept decimals; you could have a Form with a textbox named
txtAngle, bound to the ANGLE field, with two additional textboxes txtDegrees
and txtMinutes. In the AfterUpdate event of each of these latter two put
Private Sub txtDegrees_AfterUpdate
Me!txtAngle = NZ(Ne!txtDegrees) + NZ(Me!txtMinutes) / 60
End Sub
Then in the form's Current event put code like
Private Sub Form_Current()
If Not IsNull(Me.Angle) Then
Me!txtDegrees = CInt(Me.Angle)
Me!txtMinutes = 60*Me.Angle MOD 60
End IF
End Sub