#Error in calculated field of Datasheet view of query

[DocBrown]

I have the following query (stripped down to highlight the error):

SELECT Volunteers.VolunteerID, Volunteers.CommitHours,
Int(CSng(Sum([timeout]-[timein])*24)) & ":" &
Format(Int(CSng(Sum([timeout]-[timein])*1440)) Mod 60,"00") AS TotalHrs,
IIf(Not IsNull([CommitHours]),IIf([TotalHrs]>=(CStr([CommitHours]) &
":00"),True,False),null) AS Completed
FROM Volunteers LEFT JOIN VolunteerLog ON Volunteers.VolunteerID =
VolunteerLog.VolunteerID
GROUP BY Volunteers.VolunteerID, Volunteers.CommitHours;

The Volunteers table contains all the data for a particular person
(including the [CommitHours] field). the VolunteerLog table is a timesheet
where

the volunteer logs in and out ([TimeOut] and [TimeIn]) and the two tables
are related via the VolunteerID.

The "As TotalHrs" expression sums up all the time the volunteer has worked.
(I stipulate that they will never work over midnight.) Now, it's possible

that there is no entry in the [CommitHours] field AND that there might not
be any logged hours.

The problem is that where there are no logged hours, the datasheet view
displays #Error. How do I code this so either the field is blank or 0 (or

"00:00")? Also the Completed field is also displaying the #Error. The 'Not
IsNull([CommitHours]... handles that part but I've not been able to handle

the no logged hours error. I've tried some combinations of IsError() or NZ()
but haven't succeeded.


Any suggestions?

Thanks,
John

 

[Allen Browne]

Try something like this:
IIf([CommitHours] Is Null OR [TotalHrs] Is Null, False,
IIf(TotalHrs] >= DateAdd("h", [CommitHours], #0:00:00#),
True, False)) AS Completed

That's assuming that TotalHrs is a Date/Time field, and CommitHours is in
integer.

 

[DocBrown]

Sheesh, I should have known that I'd figure it out as soon as I posted a
question...

Turns out I can make it work by changing the TotalHrs expression to:

Int(CSng(nz(Sum([timeout]-[timein]),0)*24)) & ":" &
Format(Int(CSng(nz(Sum([timeout]-[timein]),0)*1440)) Mod 60,"00") AS TotalHrs,

The sum was returning the null that was giving an error to CSng.

Regards,
John