NZ Function with Time/Date Field

  • Thread starter Thread starter drum2001
  • Start date Start date
D

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
 
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
 
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 -
Click to expand...


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?
 
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 -
Click to expand...


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?
 
Back
Top