Hi Douglas
I can't see why, but if I only have a OtherHours value, the expression
doesn't return anything...
TimeWorked:
(DateDiff("n",[StartTime],[EndTime])+Nz([Lunchtime]+Nz([OtherHours]),0))/60
Can you see why it doesn't display the [OtherHours] if that's all I
have?
Thanks
Kelvin
Assuming OtherTime holds hours (not minutes like Lunchtime did, it
sounds like you want
TimeWorked:
(DateDiff("n",[StartTime],[EndTime])-Nz([Lunchtime],0))/60-Nz([OtherTime],0)
If it holds minutes, try
TimeWorked:
DateDiff("n",[StartTime],[EndTime]))-Nz([Lunchtime],0)-Nz([OtherTime],0))/60
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
I need the expression to add [OtherHours] if there is any...
If a person didn't work one day, but took personal time (OtherTime) I
need that be the ballance of time.
Thanks
Kelvin
message "no luck" doesn't really tell me much. Are you experiencing an error?
If so, what's the error? If you're not experiencing an error, what
exactly is the problem?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Hi Douglas
Thanks for your help so far...!
I need to add on more time field to this.
TimeWorked:
(DateDiff("n",[StartTime],[EndTime])-Nz([Lunchtime],0))/60
I need to add "OtherHours" to this.
I'm storing "OtherHours as LongInteger
This field can have a zero value.
I tried this a number of different ways with no luck...
+Nz([OtherHours])
Your help is appreciated!
Kelvin
message Sorry, that was a typo on my part: I'd originally meant to use "h"
in the DateDiff function to calculate hours, but using "n" for
minutes is more accurate.
Glad you got it working.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
"Kelvin Beaton" <kelvin at mccsa dot com> wrote in message
Well I thought is was, but...
I did have to modify it some...
TimeWorked:
(DateDiff("n",[StartTime],[EndTime])-Nz([Lunchtime],0))/60
Thanks
Kelvin
message Does Lunchtime have a value, or is it Null?
If it's possible that there's no value for Lunchtime, use
Expr1: DateDiff("n",[StartTime],[EndTime])-Nz([Lunchtime],0)/60
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
"Kelvin Beaton" <kelvin at mccsa dot com> wrote in message
thanks, I'll look at that idea....
This part seems to work fine,
Expr1: DateDiff("n",[StartTime],[EndTime])
Expr1: DateDiff("n",[StartTime],[EndTime])-[Lunchtime]/60
It returns nothing when I leave the "-[Lunchtime]/60" part on...
I tried these options too. non worked
Expr1: (DateDiff("n",[StartTime],[EndTime]))-([Lunchtime]/60)
Expr1: ([lunchtime]/60)-(DateDiff("n",[StartTime],[EndTime]))
Kelvin
message Store it as 05/04/2007 8:00 AM. Create a query that has two
computed fields: one using the DateValue function (so that it
only returns 05/04/2007) and one using the TimeValue function
(so that it only returns 8:00 AM). Use the query wherever you
would otherwise have used the table.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
"Kelvin Beaton" <kelvin at mccsa dot com> wrote in message
Thanks Douglas for your reply!
When you say "you should be storing the Date as part of
StartTime and EndTime".
I'm storing the date in a separate field, but I suspect you
mean in the same field as the start and end times...
would you tell me a little more about what your mean?
In the drop down, I only want them to see the time, ie 8:00 AM
not 05/04/2007 8:00 AM
Thanks
Kelvin
in message I'd recommend storing the Lunch as a Long Integer
representing the number of minutes.
Your total hours worked would then be:
DateDiff("n", [StartTime], [EndTime]) - [Lunch]/60
Note that you should be storing the Date as part of StartTime
and EndTime.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
"Kelvin Beaton" <kelvin at mccsa dot com> wrote in message
Hi there
I'm working on a data base to replace our Excel time sheet.
I need input on the data types for these fields.
Currently I have them setup this way.
StartTime (Date/Time, Medium Time) Input mask 99:00\ >LL;0;_
Lunch (Date/Time, Short Time) Input mask 00:00;0;_
(input options are 0:15, 0:30, 0:45 etc.)
EndTime (Date/Time, Medium Time) Input mask 99:00\ >LL;0;_
The Start and End Time come from a list that has a data type
of (Date/Time, Medium Time) Input mask 99:00\ >LL;0;_
I want to be able to calculate the difference between the
start and end time, then subtract the lunch time.
So 8:00 AM to 5:00 PM and subtract a 1 hour lunch. Should
come out to 8 hours worked...
Part of my question is, am I using the correct field/data
types??
Any input would be appreciated!
Thanks
Kelvin