Copying calculated values into existing fields

[Guest]

I have performed a calculation that converts degrees-minutes-seconds to
decimal degrees for latitude & longitude [LatCalc]. I now want to place
(copy)these values into the field [Latitude] that contains null values.

I can't do a direct copy for some reason...is there a way to perform this
calculation within [Latitude] on the null fields only?

 

[Jeff Boyce]

You posted in the queries newsgroup, so I'll assume you're working on a
query.

If your query includes the calculation, you could also have the query
include the [Latitude] field, and place "Null" (without the quotes) in the
Criterion row beneath it. That will select only those rows that have a null
value for [Latitude].

Now if you want to update those, based on the calculation, you can either
create a new (update) query based on the first, or possibly change the first
query into an update query.

NOTE: backup your database before doing any data updating!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

I have performed a calculation that converts degrees-minutes-seconds to
decimal degrees for latitude & longitude [LatCalc]. I now want to place
(copy)these values into the field [Latitude] that contains null values.

It's quite likely that you *don't* want to do so.

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.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

Store one or the other depiction in the table - *not both*!

I can't do a direct copy for some reason...is there a way to perform this
calculation within [Latitude] on the null fields only?

You can run an Update query, with a criterion of IS NULL on the
Latitude field, and the expression on the Update To line. Or, you can
use VBA code in a Form - it's not clear where you're doing the
calculation or how, nor what the structure of your table might be, so
I'm not sure what would be the best solution.

John W. Vinson[MVP]