Subtracting Time from a DateDiff field

[AccessIM]

I always get confused when dealing with time in Access so I will greatly
appreciate any guidance.

The [TimeSpent] field is a calculation of the difference between two times.
In the code below, I am subtracting 1 hour from [TimeSpent] to remove breaks
and lunches:

DateAdd("h",-1,[TimeSpent])

This works great as is. However, I was told that there are times when the
shift will work overtime and receive an extra break which would change the
time allotted for breaks to from 1 to 1.25.

I would like the user to be able to enter the number of time allotted for
breaks each day. I tried changing the -1 to "[Enter Break Duration]" so that
the query would prompt the user to enter the time but it doesn't work.

Is there a way to enter varying numbers in place of the -1 in the query above?

 

[tkelley via AccessMonster.com]

I think "h" is an integer. So I never could get this to work, it always
rounded to an integer:

Expr1: DateAdd("h",-1.25,[TimeSpent])

However, I did get this to work when entering -75:

Expr1: DateAdd("n",[Enter Total Break Minutes],[TimeSpent])


Did you change -1 to "[Enter Break Duration]" with quotes, or did you change
it to [Enter Break Duration].

I always get confused when dealing with time in Access so I will greatly
appreciate any guidance.

The [TimeSpent] field is a calculation of the difference between two times.
In the code below, I am subtracting 1 hour from [TimeSpent] to remove breaks
and lunches:

DateAdd("h",-1,[TimeSpent])

This works great as is. However, I was told that there are times when the
shift will work overtime and receive an extra break which would change the
time allotted for breaks to from 1 to 1.25.

I would like the user to be able to enter the number of time allotted for
breaks each day. I tried changing the -1 to "[Enter Break Duration]" so that
the query would prompt the user to enter the time but it doesn't work.

Is there a way to enter varying numbers in place of the -1 in the query above?

 

[John Spencer]

DateAdd("n",-60 * [Enter duration in hours],[Time Spent])

If they enter 1.5 the time will be changed by 90 minutes.
If they enter 1.25 the time will be changed by 75 minutes.

That said, you do realize that a dateTime field is best used to store a
point in time (date and time) and not a duration of time. Durations are
best saved as the number of seconds, minutes, or hours - depending on
the granularity you are interested in.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[AccessIM]

That was just what I was looking for. It works perfectly! Thank you so much.

DateAdd("n",-60 * [Enter duration in hours],[Time Spent])

If they enter 1.5 the time will be changed by 90 minutes.
If they enter 1.25 the time will be changed by 75 minutes.

That said, you do realize that a dateTime field is best used to store a
point in time (date and time) and not a duration of time. Durations are
best saved as the number of seconds, minutes, or hours - depending on
the granularity you are interested in.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I always get confused when dealing with time in Access so I will greatly
appreciate any guidance.

The [TimeSpent] field is a calculation of the difference between two times.
In the code below, I am subtracting 1 hour from [TimeSpent] to remove breaks
and lunches:

DateAdd("h",-1,[TimeSpent])

This works great as is. However, I was told that there are times when the
shift will work overtime and receive an extra break which would change the
time allotted for breaks to from 1 to 1.25.

I would like the user to be able to enter the number of time allotted for
breaks each day. I tried changing the -1 to "[Enter Break Duration]" so that
the query would prompt the user to enter the time but it doesn't work.

Is there a way to enter varying numbers in place of the -1 in the query above?