Query to calculate despite form fields being left blank

  • Thread starter Thread starter compostdave
  • Start date Start date
C

compostdave

Hi,

I've got a select query which is calculating how much time someone has
worked per week. The data is entered through 3 forms (Admin Time, Holiday
Time and Event time). The query works perfectly when data is input into each
form however this requires the users to do just that and they don't always
take holiday or do any admin - all input boxes are set to allow zero length
and all value inputs have a default value of zero. I understand from scouring
the web I need to put some "IsNull" code into the query somewhere but being
an access novice (and not particularly code literate!!) I haven't a clue
where it should go.

The query code is.....

SELECT tblAdvisers.AdviserName,
Sum(nz([TotalDayEventHours]+[SumOfHoursTaken]+[SumOfToilHoursTaken]+[SumOfTotalHours]))
AS TotalWeekTime, qryWeekTimePerAdviserReportAdmin.SumOfTotalHours,
qryWeekTimePerAdviserReportEvents.TotalDayEventHours,
qryWeekTimePerAdviserReportHoliday.SumOfHoursTaken,
qryWeekTimePerAdviserReportHoliday.SumOfToilHoursTaken
FROM ((tblAdvisers INNER JOIN qryWeekTimePerAdviserReportAdmin ON
tblAdvisers.AdviserName = qryWeekTimePerAdviserReportAdmin.AdviserName) INNER
JOIN qryWeekTimePerAdviserReportEvents ON tblAdvisers.AdviserName =
qryWeekTimePerAdviserReportEvents.AdviserName) INNER JOIN
qryWeekTimePerAdviserReportHoliday ON tblAdvisers.AdviserName =
qryWeekTimePerAdviserReportHoliday.AdviserName
GROUP BY tblAdvisers.AdviserName,
qryWeekTimePerAdviserReportAdmin.SumOfTotalHours,
qryWeekTimePerAdviserReportEvents.TotalDayEventHours,
qryWeekTimePerAdviserReportHoliday.SumOfHoursTaken,
qryWeekTimePerAdviserReportHoliday.SumOfToilHoursTaken;

Any suggestions gratefully received.

Thanks
Dave
 
When using the Nz function, you have to use it on each element, not the
aggragate of the elements. That is bacause if there is any one element with
Null, Null will be returned as the results.

SELECT tblAdvisers.AdviserName,
Sum(nz([TotalDayEventHours],0)+Nz([SumOfHoursTaken],0)+Nz([SumOfToilHoursTaken],0)+Nz([SumOfTotalHours],0))
AS TotalWeekTime, qryWeekTimePerAdviserReportAdmin.SumOfTotalHours,
qryWeekTimePerAdviserReportEvents.TotalDayEventHours,
qryWeekTimePerAdviserReportHoliday.SumOfHoursTaken,
qryWeekTimePerAdviserReportHoliday.SumOfToilHoursTaken
FROM ((tblAdvisers INNER JOIN qryWeekTimePerAdviserReportAdmin ON
tblAdvisers.AdviserName = qryWeekTimePerAdviserReportAdmin.AdviserName) INNER
JOIN qryWeekTimePerAdviserReportEvents ON tblAdvisers.AdviserName =
qryWeekTimePerAdviserReportEvents.AdviserName) INNER JOIN
qryWeekTimePerAdviserReportHoliday ON tblAdvisers.AdviserName =
qryWeekTimePerAdviserReportHoliday.AdviserName
GROUP BY tblAdvisers.AdviserName,
qryWeekTimePerAdviserReportAdmin.SumOfTotalHours,
qryWeekTimePerAdviserReportEvents.TotalDayEventHours,
qryWeekTimePerAdviserReportHoliday.SumOfHoursTaken,
qryWeekTimePerAdviserReportHoliday.SumOfToilHoursTaken;


--
Dave Hargis, Microsoft Access MVP


compostdave said:
Hi,

I've got a select query which is calculating how much time someone has
worked per week. The data is entered through 3 forms (Admin Time, Holiday
Time and Event time). The query works perfectly when data is input into each
form however this requires the users to do just that and they don't always
take holiday or do any admin - all input boxes are set to allow zero length
and all value inputs have a default value of zero. I understand from scouring
the web I need to put some "IsNull" code into the query somewhere but being
an access novice (and not particularly code literate!!) I haven't a clue
where it should go.

The query code is.....

SELECT tblAdvisers.AdviserName,
Sum(nz([TotalDayEventHours]+[SumOfHoursTaken]+[SumOfToilHoursTaken]+[SumOfTotalHours]))
AS TotalWeekTime, qryWeekTimePerAdviserReportAdmin.SumOfTotalHours,
qryWeekTimePerAdviserReportEvents.TotalDayEventHours,
qryWeekTimePerAdviserReportHoliday.SumOfHoursTaken,
qryWeekTimePerAdviserReportHoliday.SumOfToilHoursTaken
FROM ((tblAdvisers INNER JOIN qryWeekTimePerAdviserReportAdmin ON
tblAdvisers.AdviserName = qryWeekTimePerAdviserReportAdmin.AdviserName) INNER
JOIN qryWeekTimePerAdviserReportEvents ON tblAdvisers.AdviserName =
qryWeekTimePerAdviserReportEvents.AdviserName) INNER JOIN
qryWeekTimePerAdviserReportHoliday ON tblAdvisers.AdviserName =
qryWeekTimePerAdviserReportHoliday.AdviserName
GROUP BY tblAdvisers.AdviserName,
qryWeekTimePerAdviserReportAdmin.SumOfTotalHours,
qryWeekTimePerAdviserReportEvents.TotalDayEventHours,
qryWeekTimePerAdviserReportHoliday.SumOfHoursTaken,
qryWeekTimePerAdviserReportHoliday.SumOfToilHoursTaken;

Any suggestions gratefully received.

Thanks
Dave
Click to expand...
 
Thanks for the response, copied and pasted the below and got it to work (once
I'd change the first "nz" to "Nz").

Your help has been much appreciated!

Dave

Klatuu said:
When using the Nz function, you have to use it on each element, not the
aggragate of the elements. That is bacause if there is any one element with
Null, Null will be returned as the results.

SELECT tblAdvisers.AdviserName,
Sum(nz([TotalDayEventHours],0)+Nz([SumOfHoursTaken],0)+Nz([SumOfToilHoursTaken],0)+Nz([SumOfTotalHours],0))
AS TotalWeekTime, qryWeekTimePerAdviserReportAdmin.SumOfTotalHours,
qryWeekTimePerAdviserReportEvents.TotalDayEventHours,
qryWeekTimePerAdviserReportHoliday.SumOfHoursTaken,
qryWeekTimePerAdviserReportHoliday.SumOfToilHoursTaken
FROM ((tblAdvisers INNER JOIN qryWeekTimePerAdviserReportAdmin ON
tblAdvisers.AdviserName = qryWeekTimePerAdviserReportAdmin.AdviserName) INNER
JOIN qryWeekTimePerAdviserReportEvents ON tblAdvisers.AdviserName =
qryWeekTimePerAdviserReportEvents.AdviserName) INNER JOIN
qryWeekTimePerAdviserReportHoliday ON tblAdvisers.AdviserName =
qryWeekTimePerAdviserReportHoliday.AdviserName
GROUP BY tblAdvisers.AdviserName,
qryWeekTimePerAdviserReportAdmin.SumOfTotalHours,
qryWeekTimePerAdviserReportEvents.TotalDayEventHours,
qryWeekTimePerAdviserReportHoliday.SumOfHoursTaken,
qryWeekTimePerAdviserReportHoliday.SumOfToilHoursTaken;


--
Dave Hargis, Microsoft Access MVP


compostdave said:
Hi,

I've got a select query which is calculating how much time someone has
worked per week. The data is entered through 3 forms (Admin Time, Holiday
Time and Event time). The query works perfectly when data is input into each
form however this requires the users to do just that and they don't always
take holiday or do any admin - all input boxes are set to allow zero length
and all value inputs have a default value of zero. I understand from scouring
the web I need to put some "IsNull" code into the query somewhere but being
an access novice (and not particularly code literate!!) I haven't a clue
where it should go.

The query code is.....

SELECT tblAdvisers.AdviserName,
Sum(nz([TotalDayEventHours]+[SumOfHoursTaken]+[SumOfToilHoursTaken]+[SumOfTotalHours]))
AS TotalWeekTime, qryWeekTimePerAdviserReportAdmin.SumOfTotalHours,
qryWeekTimePerAdviserReportEvents.TotalDayEventHours,
qryWeekTimePerAdviserReportHoliday.SumOfHoursTaken,
qryWeekTimePerAdviserReportHoliday.SumOfToilHoursTaken
FROM ((tblAdvisers INNER JOIN qryWeekTimePerAdviserReportAdmin ON
tblAdvisers.AdviserName = qryWeekTimePerAdviserReportAdmin.AdviserName) INNER
JOIN qryWeekTimePerAdviserReportEvents ON tblAdvisers.AdviserName =
qryWeekTimePerAdviserReportEvents.AdviserName) INNER JOIN
qryWeekTimePerAdviserReportHoliday ON tblAdvisers.AdviserName =
qryWeekTimePerAdviserReportHoliday.AdviserName
GROUP BY tblAdvisers.AdviserName,
qryWeekTimePerAdviserReportAdmin.SumOfTotalHours,
qryWeekTimePerAdviserReportEvents.TotalDayEventHours,
qryWeekTimePerAdviserReportHoliday.SumOfHoursTaken,
qryWeekTimePerAdviserReportHoliday.SumOfToilHoursTaken;

Any suggestions gratefully received.

Thanks
Dave
Click to expand...
Click to expand...
 
Back
Top