Calculate difference between to dates

[Clemens]

I have a table with 2 date fields: [Open Date] and [Close time].
Both field have the following format: dd-mm-yyyy hh:nn.

How can I calculate the difference in days between these 2 fields and how
can I display this on a report where I want to have 4 colums like:
recordnummer - Open date - Close time - difference (or time 'record
open')

Can any one help me?

 

[Douglas J. Steele]

The difference should not be stored in the table. (Calculated values rarely,
if ever, should be stored)

Instead, create a query based on the table, and add a computed field to the
query. Use the query wherever you would otherwise have used the table.

To add the computed field in the graphical query builder, type the following
into an empty cell on the Field: row:

Difference: DateDiff("n", [Open Date], [Close Time])

That will return the difference between the two times in minutes. (using "s"
instead of "n" will return the difference in seconds) It is not possible to
return the difference in, say, hh:nn format. To do that, you'd need create a
function to convert from total minutes (or total seconds) to hh:nn (or
hh:nn:ss) format.

 

[Margaret Bartley]

I have a table with 2 date fields: [Open Date] and [Close time].
Both field have the following format: dd-mm-yyyy hh:nn.

How can I calculate the difference in days between these 2 fields and how
can I display this on a report where I want to have 4 colums like:
recordnummer - Open date - Close time - difference (or time 'record
open')

If you look at the DateDiff function documentation in the Help system, you
can see your various options.

If you want to calculate days, it would be DateDiff("d",OpenDate,CloseTime)

It will ignore the hours and minutes.