NZ Function with Time/Date Field

[drum2001]

I am working on a database for calculating worked time. Basically, I
am trying to calculate the total time per day. My logic is:
SUM(ENDTIME - STARTTIME) - SUM(LUNCHOUT-LUNCHIN). This works great in
my query! However, if a user does not take a lunch, then the
calculation does not return a value.

Is there anyway to default the LUNCHOUT and LUNCHIN to "00:00:00" if
there is no value? Similary to SUM(NZ(LUNCHOUT-LUNCHIN),
#00:00:00# )) so the calculation can still complete.

Any suggestions are appreciated. Thanks!

Brad

 

[Dale Fye]

Brad,

Are these (EndTime, StartTime, LunchIn, LunchOut) date/time fields?

If so, then subtracting them will still give you a double precision value
that represents the fraction of a day (.333 = 8 hours). Do you really want
to do your math using these values, or do you want to sum the minutes, in
which case you might use the DateDiff("n", StartTime, EndTime) to compute the
number of minutes between the start and end times?

As far as defaulting the LunchOut and LunchIn times, you can certainly do
that using:

Datediff("n", NZ(LunchIn, 0), NZ(LunchOut, 0))

However, there are some issues here.

1. What if they log out and forget to log back in?
2. Do you have shifts? If so, do the shifts run from one day to another?

You might want to consider writing a function which you pass all 4 values
to. This would allow you to use some business logic to deal with situations
where one or more of these values is missing, but others are present.

HTH
Dale

 

[drum2001]

Brad,

Are these (EndTime, StartTime, LunchIn, LunchOut) date/time fields?  

If so, then subtracting them will still give you a double precision value
that represents the fraction of a day (.333 = 8 hours).  Do you reallywant
to do your math using these values, or do you want to sum the minutes, in
which case you might use the DateDiff("n", StartTime, EndTime) to compute the
number of minutes between the start and end times?

As far as defaulting the LunchOut and LunchIn times, you can certainly do
that using:

Datediff("n", NZ(LunchIn, 0), NZ(LunchOut, 0))

However, there are some issues here.

1.  What if they log out and forget to log back in?
2.  Do you have shifts?  If so, do the shifts run from one day to another?

You might want to consider writing a function which you pass all 4 values
to.  This would allow you to use some business logic to deal with situations
where one or more of these values is missing, but others are present.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.








- Show quoted text -

Dale,

Thank you for the information!

Yes, the fields are date/time fields.
If the users forget to punch in/out, the admin has the ability to
insert the missing record so Im not concerned.

I am not sure which direction to go, but I am ok with keeping the same
format.

I tried the NZ you listed above but I received the following error:

"You tried to execute a querry that does not include the specified
expression DateDiff("n",NZ([QueryLunchIn].[Clock],
0),NZ([QueryLunchOut].[Clock],0)) as part of an aggregate
function." (I did update the field names accordingly)

Any suggestions?

 

[Dale Fye]

It looks like you are trying to do some sort of an aggregation query. My
guess is you need to add a SUM to the Total line in the query grid.

Dale

Brad,

Are these (EndTime, StartTime, LunchIn, LunchOut) date/time fields?

If so, then subtracting them will still give you a double precision value
that represents the fraction of a day (.333 = 8 hours). Do you really want
to do your math using these values, or do you want to sum the minutes, in
which case you might use the DateDiff("n", StartTime, EndTime) to compute
the
number of minutes between the start and end times?

As far as defaulting the LunchOut and LunchIn times, you can certainly do
that using:

Datediff("n", NZ(LunchIn, 0), NZ(LunchOut, 0))

However, there are some issues here.

1. What if they log out and forget to log back in?
2. Do you have shifts? If so, do the shifts run from one day to another?

You might want to consider writing a function which you pass all 4 values
to. This would allow you to use some business logic to deal with
situations
where one or more of these values is missing, but others are present.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.








- Show quoted text -

Dale,

Thank you for the information!

Yes, the fields are date/time fields.
If the users forget to punch in/out, the admin has the ability to
insert the missing record so Im not concerned.

I am not sure which direction to go, but I am ok with keeping the same
format.

I tried the NZ you listed above but I received the following error:

"You tried to execute a querry that does not include the specified
expression DateDiff("n",NZ([QueryLunchIn].[Clock],
0),NZ([QueryLunchOut].[Clock],0)) as part of an aggregate
function." (I did update the field names accordingly)

Any suggestions?