Hello again. I am trying to create a query and here is what I have currently:
SELECT Count(*) AS Count_Action_Items, Sum(IIf(Actual_Completion_Date<=Target_Completion_Date And Actual_Completion_Date >#1/1/2000#,1,0)) AS Sum_On_Time,
Sum(IIf(Target_Completion_Date < Date(Now,"mm/dd/yy") Or Actual_Completion_Date>Target_Completion_Date,1,0)) AS Sum_Late, Find_Days_Late() AS Average_Days_Late
FROM [Special Requests]
WHERE ((([Special Requests].Date_Requested) Between [Forms]![Presage Search]![Start_Date] And [Forms]![Presage Search]![End Date]))
GROUP BY Find_Days_Late();
I bolded the part of this query that is giving me a headache. I want to check if the Target Completion Date is less than the current date. I thought the Date(Now) would work here but I am getting an error message saying 'Wrong number of arguments used with function in query expression "Sum(IIf(Target_Completion_Date < Date(Now,"mm/dd/yy") Or Actual_Completion_Date>Target_Completion_Date,1,0)) AS Sum_Late".
Anybody have an idea what I am doing wrong here. Or maybe a better way to use the current date in an IIF statement
Thanks
SELECT Count(*) AS Count_Action_Items, Sum(IIf(Actual_Completion_Date<=Target_Completion_Date And Actual_Completion_Date >#1/1/2000#,1,0)) AS Sum_On_Time,
Sum(IIf(Target_Completion_Date < Date(Now,"mm/dd/yy") Or Actual_Completion_Date>Target_Completion_Date,1,0)) AS Sum_Late, Find_Days_Late() AS Average_Days_Late
FROM [Special Requests]
WHERE ((([Special Requests].Date_Requested) Between [Forms]![Presage Search]![Start_Date] And [Forms]![Presage Search]![End Date]))
GROUP BY Find_Days_Late();
I bolded the part of this query that is giving me a headache. I want to check if the Target Completion Date is less than the current date. I thought the Date(Now) would work here but I am getting an error message saying 'Wrong number of arguments used with function in query expression "Sum(IIf(Target_Completion_Date < Date(Now,"mm/dd/yy") Or Actual_Completion_Date>Target_Completion_Date,1,0)) AS Sum_Late".
Anybody have an idea what I am doing wrong here. Or maybe a better way to use the current date in an IIF statement
Thanks