Calculating Fields

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"gp" wrote:

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.

On Tue, 23 Feb 2010 09:37:02 -0800, gp wrote:

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

John W. Vinson [MVP]

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.
"John W. Vinson" wrote:

On Tue, 23 Feb 2010 09:37:02 -0800, gp wrote:

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

John W. Vinson [MVP]
.

Use a query as the basis for the ArcGis plots, not the base table, e.g.

SELECT Helispot,
Degrees + Minutes/60 As DecimalDegrees
FROM Helispots;

The query will always reflect the values in the Helispots table, so there can
be no risk of inconsistent data arising from the redundancy which assigning
the computed decimal degrees values to a column in the table would introduce.

Ken Sheridan
Stafford, England

gp wrote:
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.

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
[quoted text clipped - 33 lines]
End IF
End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201002/1