Calculating Fields

  • Thread starter Thread starter gp
  • Start date Start date
G

gp

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.
 
If you are doing this at table level, you can't.

However you can caculate it in a query. You might just want to put [ ]
around the Minutes... Wait a second! Do you have a / in the field name? That
can cause problems. The only non-alphabetical character that you should ever
use in field, table, or query name is the underscore _ . Don't even use
spaces.

Actually you should not store the DECIMAL_DEGREES. Instead you should do the
math in a query, form, or report as needed. For the most part, you shouldn't
store derived data in a table. An example would be a Date of Birth and Age
field. You can always figure the age from the date of birth, therefore don't
store the Age.
 
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.
Click to expand...

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
 
Okay, I guess I need to take a formal class on this. Here is what I'm trying
to do. I'm want to create a database of our helispots that I can use to plot
them in ArcGis. The problem is that we use degess and decimal minutes but
ArcGis will only take decimal minutes, so I need to convert the data we have
to decimal degrees. I want to be able to enter new data into the database
and have it populate the field decimal degrees so I can plot it. I tried
creating a form, everything worked but it didn't populate the table. I've
read the help file and because I'm not familiar with Access I didn't seem to
find the answers. The original table was created from an Excel spreadsheet.
Sorry for not understanding. Thanks.
 
Back
Top