Subtracting one time from another time

I have two fields on a form.  One is "StartTime"  The other is "EndTime"  

I am trying to subtract StartTime from EndTime and get the right result.

If StartTime is 10:25 am and StopTime is 11:00 am I want to see 00:35 as the result.

In my result field I have:

=[txtStopTime]-[txtStartTime]

But I'm getting 0;45

????

--Steve
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Try with:

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

/gustav
0
PatHartmanCommented:
Use DateDiff() to subtract dates.
0
SteveL13Author Commented:
Hmmm.  I'm getting 0:45 instead of 0:35  (minutes)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Dale FyeCommented:
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.
0
Dale FyeCommented:
Will you ever have an instance where the txtStartTime is before midnight and txtStopTime is after midnight (the next morning)?
0
Gustav BrockCIOCommented:
CVDate([txtStopTime])-CVDate([txtStartTime]) is the clean method and will return 0:35.
So something else is going on.

/gustav
0
PatHartmanCommented:
@Dale,
Minutes is "nn"
so, the expression should be:

=format([txtStopTime]-[txtStartTime], "hh:nn")
0
SteveL13Author Commented:
All suggestions have returned 0:45

??

Bot the start and end time fields are short time format.  Might this be a problem?
0
Dale FyeCommented:
@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".
0
Dale FyeCommented:
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.
0
SteveL13Author Commented:
They are Date/Time, General Date fields in the table.
0
Dale FyeCommented:
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.
0
SteveL13Author Commented:
Here you go...

Start Stop Screen Shot
0
Dale FyeCommented:
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.
0
SteveL13Author Commented:
Here it is...

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

??
0
Dale FyeCommented:
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.
0
Gustav BrockCIOCommented:
> So why is the result ok here but not on the form?

Field names:

    [Load Sch Complete]
    [Load Sch Start]

are different from your original data.

Anyway, as these are bound to fields of datatype Date, you can use the expression

    =[Load Sch Complete]-[Load Sch Start]

and specify the Format property of the textbox as:

    hh:nn

 /gustav
0
SteveL13Author Commented:
Now when I enter hh:nn as the format it automatically changes itself to Short Time.

And the result is still 0:45
0
Dale FyeCommented:
So, do as I recommended and change the recordsource of the form to a query which contains the computed column as defined in my previous post.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.