Query calculation not working in form unless all fields contain va

[Guest]

I've created a database to enter and access weekly employee schedules. I
have created a query that calculates hours worked per day, for each day
Sun-Sat. I also created a "weekly total # hrs" to calculate how many total
hrs per week an employee is scheduled for. I based the "weekly total # hrs"
on an sum function of all total # hrs for each day, Sun -Sat (entered these
individually in the query, for ex.
=[TotalSunday]+[TotalMonday]+[TotalTuesday]...etc.)

I created a form for entry of schedules, but have run into an issue that I
am sure is fairly easy to resolve - I just don't know how. Basically, I need
to be able to enter a checkbox field for each day (Sun-Sat) that would be
marked if the employee is not scheduled to work that day. As the form is
now, I do not have such an option, which I think is messing up my "weekly
total # hrs" calculation b/c the calculation does not show unless I have
entered shift values for each day of the week. I need to know how to make
this checkbox so that access does not consider a missing value in the total
calculation, b/c I am sure that is what is complicating my weekly total.

Thanks, I'd greatly appreciate any help with this issue.

 

[Michel Walsh]

Hi,

=Nz([TotalSunday],0)+Nz([TotalMonday],0)+Nz([TotalTuesday],0) + ...



The reason is that the Null value propagate. See Null as meaning "unknown,
yet". So, what is 4 + unknown ? well, it is unknown, which is null. So,
as soon as one null is in the formula, the result is also null. Using Nz, as
we did, change that null for zero (which is known, it is zero, it is not
unknown anymore).


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks! Worked like a charm...

And your explanation totally makes sense, thanks for explaining so well and
helping me out.

Kate

Hi,

=Nz([TotalSunday],0)+Nz([TotalMonday],0)+Nz([TotalTuesday],0) + ...



The reason is that the Null value propagate. See Null as meaning "unknown,
yet". So, what is 4 + unknown ? well, it is unknown, which is null. So,
as soon as one null is in the formula, the result is also null. Using Nz, as
we did, change that null for zero (which is known, it is zero, it is not
unknown anymore).


Hoping it may help,
Vanderghast, Access MVP

I've created a database to enter and access weekly employee schedules. I
have created a query that calculates hours worked per day, for each day
Sun-Sat. I also created a "weekly total # hrs" to calculate how many
total
hrs per week an employee is scheduled for. I based the "weekly total #
hrs"
on an sum function of all total # hrs for each day, Sun -Sat (entered
these
individually in the query, for ex.
=[TotalSunday]+[TotalMonday]+[TotalTuesday]...etc.)

I created a form for entry of schedules, but have run into an issue that I
am sure is fairly easy to resolve - I just don't know how. Basically, I
need
to be able to enter a checkbox field for each day (Sun-Sat) that would be
marked if the employee is not scheduled to work that day. As the form is
now, I do not have such an option, which I think is messing up my "weekly
total # hrs" calculation b/c the calculation does not show unless I have
entered shift values for each day of the week. I need to know how to make
this checkbox so that access does not consider a missing value in the
total
calculation, b/c I am sure that is what is complicating my weekly total.

Thanks, I'd greatly appreciate any help with this issue.

 

[Gouri]

Kate,
Can you please send how the timesheet and the related forms are created. I
am looking to create the time sheet data base which keeps track of the
employees time for a project on daily/weekly basis.
Thanks,
Gouri