Expression in Query

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

A question about Expressions in Queries.

I work flexible hours and want to use Access to find out if I have
worked too long (> 8:30) or too short (<8:30) on a given day.

My simple table has three fields:
EnterOffice (e.g. value 8:15 AM)
LeaveOffice (e.g. value 3:40 PM)
StandardTime (value 8:30)

In a Query I have this Expression:
LeaveOffice - EnterOffice - StandardTime

The result = 1:05 whereas I would have liked to get -1:05, in other
words: the outcome seems to be an absolute number.

Any solutions?
Thanks - Rob
 
Rob

What data type are your fields? Are they Date/Time? Are you only storing a
time value (you might not realize it, but you ARE storing a date portion
too)?

How are you finding the difference? Are you using a query and the
DateDiff() function?

How are you getting an hh:mm-formatted result? Are you starting with
minutes and "parsing" that into hh:mm?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Time comes in two flavors - elapsed duration (I worked 3 hours) and instant
in time (It is 3 AM). Your calculation ends up giving you an instant in
time (which is never negative).

I would try using the DateDiff function to convert your Start time and End
time into a duration and then subtract that to get number minutes over or
under.

DateDiff("n",LeaveOffice,EnterOffice) - 510 will give you the number of
minutes over or under a duration of 8 hours and 30 minutes.

If StandardTime is a date field and varies you can use
DateDiff("n",StandardTime,0) to get the minutes

DateDiff("n",LeaveOffice,EnterOffice) - DateDiff("n",StandardTime,0)

To Format that nicely into hours and minutes and get a negative sign,
requires a bit of arithmetic and formatting. Replace X with the formula of
choice above

IIF(X<0,"-","") & ABS(X\60) & Format(ABS(X Mod 60),"\:00")
 
John said:
Time comes in two flavors - elapsed duration (I worked 3 hours) and instant
in time (It is 3 AM). Your calculation ends up giving you an instant in
time (which is never negative).

I would try using the DateDiff function to convert your Start time and End
time into a duration and then subtract that to get number minutes over or
under.

DateDiff("n",LeaveOffice,EnterOffice) - 510 will give you the number of
minutes over or under a duration of 8 hours and 30 minutes.

If StandardTime is a date field and varies you can use
DateDiff("n",StandardTime,0) to get the minutes

DateDiff("n",LeaveOffice,EnterOffice) - DateDiff("n",StandardTime,0)

To Format that nicely into hours and minutes and get a negative sign,
requires a bit of arithmetic and formatting. Replace X with the formula of
choice above

IIF(X<0,"-","") & ABS(X\60) & Format(ABS(X Mod 60),"\:00")
Click to expand...

Thanks guys,

I was looking for a simple solution. My trainees try to solve this in
Excel but in the "1900" (as opposed to 1904) Date mode, negative times
are presented as #####

In Windows Excel enter:
A1 = 7:55
A2 = 8:00

In A3 formula = A1-A2

Result = #####

Removing formatting however shows a negative number.
If you switch to the "1904" date system, you're all set.

I know how to cope with this but I was looking for a comprehensable
solution to show my trainees.

Yes, I used Date/Time fields.

Have solved this in FileMaker but as most people who attend my courses
use Microsoft Office, I was trying to find a substitute for Excel in Access.

Thanks again - Robert
 
Back
Top