Question on using the current date in IIF (1 Viewer)

[hammerva]

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

 

[dcx693]

I didn't really look at the rest of the query, but this expression:
Date(Now,"mm/dd/yy")
is definitely not right. The Date function (sometimes shown with () after it - as in Date() ) does not take parameters. The Now function works the same way. The Date() function returns the current system date. The Now() function returns the system date and time.

 

[hammerva]

That is true. Almost every time I use a Date() statement I am putting the result in a field. This time I have no field

So is there a way to use the current date in a where check? I tried using CURRENT DATE which I saw in my DB2 book but got a syntax error near the field.

 

[boblarson]

IIf(Target_Completion_Date < Date(Now,"mm/dd/yy") Or Actual_Completion_Date>Target_Completion_Date,1,0))

Should be

IIf(Target_Completion_Date < Date() Or Actual_Completion_Date>Target_Completion_Date,1,0))

 

[hammerva]

Works like a charm now

Thanks