ElapsedTime (> 24hours) through ms.query. 1

[acjim]

Hi there,

I've got an Excel worksheet linked via ms.query to an query within an Access dB that extracts records from ODBC linked tables.
This Access query has to calculate the time difference between either Arrival and Discharge or Arrival and Transfer. This can result in greater than 24 hour periods. A well established issue within Access.

I've so far solved the problem by including the ElapsedTime function (from Microsoft's technical pages). This works very well if the query is run in Access but is not recognised thru the ms.query link. I know this cannot be gotten to work so can you help?

Is there a way of including the functionality of ElapsedTime in the Query Grid? or SQL script?

The other option I've read about seems to be in the Front-End, (excel?) how would I set this up? I've already taken this particular report away from excel macros that took an age to run....


Many Thanks!

Jim

 

[Golom]

Can you clarify? The 24-hour limit applies to the content of a datetime or timestamp field but the difference between two such fields (i.e. the elapsed time) has no such restriction. For example

    DateDiff ("h", #01/01/2003#, #02/01/2003#)
    DateDiff ("n", #01/01/2003#, #02/01/2003#)

Yield 744 hours or 44,640 minutes with no problems. You can use the DateDiff function in an SQL Statement like this

    Select ..., DateDiff ("n", ArrivalDate, DisChargeDate ) As [ElapsedMinutes]

 

[acjim]

Golom,

You sir/madam are a friend and scholar - I obviously couldn't see the wood for the trees.

Why I didn't see that earlier!

Oh well I just hope that all my dead-ended experiments will come in handy at some point.

Thanks Again

jim