Subtracting one time from another time

Try with:

=CVDate([txtStopTime])-CVDate([txtStartTime])

/gustav

Use DateDiff() to subtract dates.

Hmmm.  I'm getting 0:45 instead of 0:35  (minutes)

How about:

=format([txtStopTime]-[txtStartTime], "hh:mm")

But what if one of those values is missing(NULL)?  In that case, this will return a NULL value.

Will you ever have an instance where the txtStartTime is before midnight and txtStopTime is after midnight (the next morning)?

CVDate([txtStopTime])-CVDate([txtStartTime]) is the clean method and will return 0:35.
So something else is going on.

/gustav

All suggestions have returned 0:45

??

Bot the start and end time fields are short time format.  Might this be a problem?

@Pat,

Yes, minutes is generally 'nn', but in this format VBA will interpret it as minutes anyway; try it.  But good point, it should be "hh:nn".

Then your times are not what you think they are.

Short Time simply reflects how the data is displayed.  Remove the formatting on that field to see the full data.

They are Date/Time, General Date fields in the table.

Open your table in design view, change the format of those fields (assuming they are bound controls) to nothing.  Then open the table scroll to the row with the data you are trying to view, take a screen shot, and post it here.

Here you go...

OK, now try a query.

SELECT [Load Sch Start], [Load Sch Complete]
, Format([Load Sch Complete] - [Load Sch Start], "hh:nn") as LoadTime
FROM yourTable

and display those results.

Here it is...


So why is the result ok here but not on the form?

??

Good question, which I cannot answer, unless the textboxes on the form are not actually bound to the right fields in your table.  Generally, I give the textboxes names that are identical to the name of the field, except with a prefix:

txt_Load_Sch_Start
txt_Load_Sch_Complete

You could simply modify the RecordSource of your form to a query which contains that formatted text.  The control would be blank if either [Load Sch Start] or [Load Sch Complete] was NULL, but would recalculate as each of those values was changed.

Now when I enter hh:nn as the format it automatically changes itself to Short Time.

And the result is still 0:45

Because of my own inability to provide real field names and also to even use them myself I feel I should grant awards to all who helped.  I am sorry for all the confusion.