Time Calculations in Merged field

[Bob G]

Not sure If I need to post this here or in Access NG.
(Will probably do both) Trying to calculate time
difference (End time - start time) in a field merged from
Access 2000 to Word 2000 document. Simple {= endtime-
starttime} doesn't work. Any thoughts?

 

[Cindy M -WordMVP-]

Hi Bob,

Not sure If I need to post this here or in Access NG.
(Will probably do both) Trying to calculate time
difference (End time - start time) in a field merged from
Access 2000 to Word 2000 document. Simple {= endtime-
starttime} doesn't work. Any thoughts?

You should definitely be doing this on the Access side,
using an EXPRESSION in a query. You'll find an example of
how to do this on my website, if you've never had to do a
calculation in an Access query, before

Word basically canNOT do date calculations. There are
really, really, really complicated ways to do some using
fields. But if you have the choice of using Access, then
you'll save yourself lots of time and frustration by just
doing it there

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[macropod]

Hi Bob,

For both times, you'll need to convert the hours, minutes and seconds to
seconds, do the subtraction, the convert the result back to hours, minutes
and seconds to seconds. To do this you'll need a compound field, like:

{QUOTE
{SET Time1 23:45:00}
{SET Time2 12:34:56}
{SET TimeDiff{=({Time1 \@ HH}*3600+{Time1 \@ mm}*60+{Time1 \@ ss})+({Time2
\@ HH}*3600+{Time2 \@ mm}*60+{Time2 \@ ss})}}
{=INT({TimeDiff}/3600)}:{=INT(({TimeDiff}-INT({TimeDiff}/3600)*3600)/60) \#
00}:{=MOD({TimeDiff},60) \# 00}}

This is all one field, laid out to make it easier to read. For demo
purposes, I've used SET fields for Time1 and Time2, where you'd have
MAILMERGE fields.

Cheers