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
|
|||
|
|||
#Error in calculated field of Datasheet view of query
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 |
#2
|
|||
|
|||
#Error in calculated field of Datasheet view of query
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "DocBrown" wrote in message ... 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 |
#3
|
|||
|
|||
#Error in calculated field of Datasheet view of query
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 "DocBrown" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|