# I need to calculate in time, out time and lunch...

K

#### Kelvin Beaton

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

D

#### Douglas J. Steele

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.

K

#### Kelvin Beaton

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 said:
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 said:
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

D

#### Douglas J. Steele

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 said:
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 said:
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 said:
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

K

#### Kelvin Beaton

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 said:
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 said:
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 said:
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

D

#### Douglas J. Steele

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 said:
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 said:
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 said:
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

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

K

#### Kelvin Beaton

That works great!
Yes there was no lunch time entered...

Thanks again for your time!

Kelvin

Douglas J. Steele said:
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 said:
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 said:
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

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

K

#### Kelvin Beaton

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 said:
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 said:
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 said:
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

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

D

#### Douglas J. Steele

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 said:
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 said:
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 said:
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

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

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

K

#### Kelvin Beaton

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 said:
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 said:
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 said:
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

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

D

#### Douglas J. Steele

"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)

Kelvin Beaton said:
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 said:
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 said:
Well I thought is was, but...

I did have to modify it some...
TimeWorked: (DateDiff("n",[StartTime],[EndTime])-Nz([Lunchtime],0))/60

Thanks

Kelvin

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

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

K

#### Kelvin Beaton

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 said:
"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)

Kelvin Beaton said:
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 said:
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

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

D

#### Douglas J. Steele

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!)

Kelvin Beaton said:
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 said:
"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)

Kelvin Beaton said:
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

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

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

K

#### Kelvin Beaton

thanks, that's what I needed!

Kelvin

Douglas J. Steele said:
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!)

Kelvin Beaton said:
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 said:
"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

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

K

#### Kelvin Beaton

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 said:
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!)

Kelvin Beaton said:
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 said:
"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

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

D

#### Douglas J. Steele

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

(no e-mails, please!)

Kelvin Beaton said:
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 said:
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!)

Kelvin Beaton said:
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

"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

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

K

#### Kelvin Beaton

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 said:
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

(no e-mails, please!)

Kelvin Beaton said:
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 said:
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

D

#### Douglas J. Steele

Sorry, I don't download applications.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Kelvin Beaton said:
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 said:
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

(no e-mails, please!)

Kelvin Beaton said:
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

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.