If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
I need to calculate in time, out time and lunch...
"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 http://I.Am/DougSteele (no private e-mails, please) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote 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 http://I.Am/DougSteele (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 |
#12
|
|||
|
|||
I need to calculate in time, out time and lunch...
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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (no private e-mails, please) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote 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 http://I.Am/DougSteele (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 |
#13
|
|||
|
|||
I need to calculate in time, out time and lunch...
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 http://I.Am/DougSteele (no e-mails, please!) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (no private e-mails, please) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote 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 http://I.Am/DougSteele (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 |
#14
|
|||
|
|||
I need to calculate in time, out time and lunch...
thanks, that's what I needed!
Kelvin "Douglas J. Steele" wrote in message ... 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 http://I.Am/DougSteele (no e-mails, please!) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (no private e-mails, please) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote 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 http://I.Am/DougSteele (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 |
#15
|
|||
|
|||
I need to calculate in time, out time and lunch...
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 "Douglas J. Steele" wrote in message ... 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 http://I.Am/DougSteele (no e-mails, please!) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (no private e-mails, please) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote 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 http://I.Am/DougSteele (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 |
#16
|
|||
|
|||
I need to calculate in time, out time and lunch...
Couple of things (one of them an apparent typo on your part).
Whenever you throw a Null into an equation, it results in Null, so you need to wrap Nz( ) around it. If one or both of StartTime and EndTime can be Null, that means you need Nz around it. The apparent typo is with the Lunchtime: your parentheses are incorrect. Here's what I think you need: (Nz(DateDiff("n",[StartTime],[EndTime]),0)-Nz([Lunchtime],0)-Nz([OtherTime],0))/60 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Kelvin Beaton" kelvin at mccsa dot com wrote in message ... 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 "Douglas J. Steele" wrote in message ... 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 http://I.Am/DougSteele (no e-mails, please!) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (no private e-mails, please) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote 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 http://I.Am/DougSteele (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 |
#17
|
|||
|
|||
I need to calculate in time, out time and lunch...
Thanks Doug
Would you care to give me some input on the project I'm working on? This is the Access version of our timesheet I'm working on. http://www.mccsa.com/Temp/TimeAndBilling.zip This is the excel file I'm trying to replicate. http://www.mccsa.com/Temp/Timesheet0607R1.zip I'm not sure if I need to take a totaly different approach to the structure.... I know this approach will create blank records, but I think I want to dispay the two week pay period.... Any input would be appreciated. Thanks Kelvin "Douglas J. Steele" wrote in message ... Couple of things (one of them an apparent typo on your part). Whenever you throw a Null into an equation, it results in Null, so you need to wrap Nz( ) around it. If one or both of StartTime and EndTime can be Null, that means you need Nz around it. The apparent typo is with the Lunchtime: your parentheses are incorrect. Here's what I think you need: (Nz(DateDiff("n",[StartTime],[EndTime]),0)-Nz([Lunchtime],0)-Nz([OtherTime],0))/60 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Kelvin Beaton" kelvin at mccsa dot com wrote in message ... 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 "Douglas J. Steele" wrote in message ... 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 http://I.Am/DougSteele (no e-mails, please!) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (no private e-mails, please) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote 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 http://I.Am/DougSteele (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 |
#18
|
|||
|
|||
I need to calculate in time, out time and lunch...
Sorry, I don't download applications.
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Kelvin Beaton" kelvin at mccsa dot com wrote in message ... Thanks Doug Would you care to give me some input on the project I'm working on? This is the Access version of our timesheet I'm working on. http://www.mccsa.com/Temp/TimeAndBilling.zip This is the excel file I'm trying to replicate. http://www.mccsa.com/Temp/Timesheet0607R1.zip I'm not sure if I need to take a totaly different approach to the structure.... I know this approach will create blank records, but I think I want to dispay the two week pay period.... Any input would be appreciated. Thanks Kelvin "Douglas J. Steele" wrote in message ... Couple of things (one of them an apparent typo on your part). Whenever you throw a Null into an equation, it results in Null, so you need to wrap Nz( ) around it. If one or both of StartTime and EndTime can be Null, that means you need Nz around it. The apparent typo is with the Lunchtime: your parentheses are incorrect. Here's what I think you need: (Nz(DateDiff("n",[StartTime],[EndTime]),0)-Nz([Lunchtime],0)-Nz([OtherTime],0))/60 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Kelvin Beaton" kelvin at mccsa dot com wrote in message ... 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 "Douglas J. Steele" wrote in message ... 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 http://I.Am/DougSteele (no e-mails, please!) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (no private e-mails, please) "Kelvin Beaton" wrote in message ... 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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote in 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 http://I.Am/DougSteele (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 "Douglas J. Steele" wrote 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 http://I.Am/DougSteele (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 |
|
Thread Tools | |
Display Modes | |
|
|