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 |
#1
|
|||
|
|||
Query calculation not working in form unless all fields contain va
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. |
#2
|
|||
|
|||
Query calculation not working in form unless all fields contain va
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 "KateCee" wrote in message ... 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. |
#3
|
|||
|
|||
Query calculation not working in form unless all fields contai
Thanks! Worked like a charm...
And your explanation totally makes sense, thanks for explaining so well and helping me out. Kate "Michel Walsh" wrote: 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 "KateCee" wrote in message ... 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. |
#4
|
|||
|
|||
Query calculation not working in form unless all fields contain va
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 "KateCee" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|