Subtracting two fields in SQL syntax HELP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

At one point one field is updated in a table. Later another field is updated.
Now, using SQL, I want to subtract the two and put the result into a third
column.

I normally work in Excel, not Access (as if that's not completely obvious).
I can't get the syntax right. My string follows, can someone please correct
it for me? When I attempt to run it I get "External name not defined":

strSQL = "UPDATE [" & strTableName & "] SET ELAPSED_TIME = (" &
Format([TIMER_TABLE].[END_TIME] - 1 - [TIMER_TABLE].[START_TIME], "DD
HH:MM:SS") & ")"
 
quartz said:
At one point one field is updated in a table. Later another field is updated.
Now, using SQL, I want to subtract the two and put the result into a third
column.

I normally work in Excel, not Access (as if that's not completely
Click to expand...
obvious).

The above is obvious and is the cause of your grief. The third field
should not exist in the table at all. Delete it and create a query based
on the table adding the deleted field as a calculated expression.

That is what you would do in Excel right? You would have an *expression*
in the third column that did the calculation on the fly. What you are
doing now is the Excel equivelant of writing a macro that takes all of the
values in column 1 and column 2, does some math on them and places the
result in column 3 as a hard value. Sounds like a silly thing to do
doesn't it?
 
Rick, I see where you are going, but it really depends upon what you need and
what info you need to store. Actually, I need a start time, an end time and
for the convenience of the user, the time difference. Can you correct my SQL?
 
Rick, I see where you are going, but it really depends upon what you need and
what info you need to store. Actually, I need a start time, an end time and
for the convenience of the user, the time difference.
Click to expand...

I'm sure you do need this. What Rick and I are suggesting is the best
way to provide the user with that information.

A Query provides a recordsource which the user can see on a datasheet,
on a Form, on a report, on an export, or as the basis for another
query. If you're assuming that the time difference must be stored in a
table field in order to be available to the user, that assumption is
incorrect.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Rick Brandt said:
That is what you would do in Excel right? You would have an *expression*
in the third column that did the calculation on the fly. What you are
doing now is the Excel equivelant of writing a macro that takes all of the
values in column 1 and column 2, does some math on them and places the
result in column 3 as a hard value. Sounds like a silly thing to do
doesn't it?
Click to expand...

There are situations when this isn't silly. Formulas have overhead
e.g. file bloat. Replacing formulas with values where the dependent
values are stable is legitimate i.e. why have a formula if will never
get recalculated? Also, there are certain situations where having a
cell formula is a hindrance e.g. ever tried changing a cell containing
a formula from a Jet (MS Access) query? How about just changing one of
the dependent cells using Jet and getting the formula cell to
recalculate?

Jamie.

--
 
Back
Top