Substract Time from Expression (1 Viewer)

N

Novice1

Registered User.

Local time

Today, 15:51

Joined

Mar 9, 2004

Messages

385

• Feb 5, 2016

• #1

How do I subtract 1 hour from this expression? Thanks

=Int(Sum([MondayTimeOut]-[MondayTimeIn])) & Format(Sum([MondayTimeOut]-[MondayTimeIn]),"h"" hr ""n"" min """)

 

Uncle Gizmo

Nifty Access Guy

Staff member

Local time

Today, 23:51

Joined

Jul 9, 2003

Messages

16,273

• Feb 5, 2016

• #2

YouTube Video MS Access - Add Time to a Date

 

MarkK

bit cruncher

Local time

Today, 15:51

Joined

Mar 17, 2004

Messages

8,180

• Feb 5, 2016

• #3

FWIW, in a VBA Date and Jet/ACE Date, 1 day = 1.0, so one hour = 1/24 (which is one day divided by 24 hours).

 

N

Novice1

Registered User.

Local time

Today, 15:51

Joined

Mar 9, 2004

Messages

385

• Feb 6, 2016

• #4

I hear you but I'm still clueless how to add the expression

=Int((Sum([MondayTimeOut]-[MondayTimeIn])) -.0416666) & Format(Sum([MondayTimeOut]-[MondayTimeIn]),"h"" hr ""n"" min """)

 

Y

ypma

Registered User.

Local time

Today, 23:51

Joined

Apr 13, 2012

Messages

643

• Feb 6, 2016

• #5

Is puting a add 1 hour in the after update of the MondayTimeOut date field worth a consideration ?

#Private Sub MondayTimeOut_AfterUpdate()
Me.MondayTimeOut = DateAdd("h", 1, MondayTimeOut)
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.Text12.Requery

End Sub#
Just a thought
Regards Ypma

 

MarkK

bit cruncher

Local time

Today, 15:51

Joined

Mar 17, 2004

Messages

8,180

• Feb 6, 2016

• #6

I would write an intermediate expression in a different control, since you are already doing this sum twice. Do the sum once in a hidden control, then in a different control add/subtract the time. You don't have to solve this whole thing in one expression. Get pieces of it working, then add more pieces. IMO.