Syntax problem with DateValue() in query

[papadega3000]

I have a select query with a number of expressions and I cannot seem to get the expressions to work. Access keeps giving me syntax error on the following code:

SELECT Run.Test_Case, 
 
Sum(IIf(DateValue([Attempted_Actual]) < DateValue([Attempted_Actual]),[Points],0))/Sum(IIf(Not IsNull([Attempted_Actual]),[Points],0))*100 AS ActualAttempted,
Sum(IIf(DateValue([Completed_Actual]) < DateValue([Completed_Actual]),[Points],0))/Sum(IIf(Not IsNull([Completed_Actual]),[Points],0))*100 AS ActualCompleted,
Sum(IIf(DateValue([Verified_Actual]) < DateValue([Verified_Actual]),[Points],0))/Sum(IIf(Not IsNull([Verified_Actual]),[Points],0))*100 AS ActualClosed,


Sum(IIf(DateValue([Attempted_Planned]) < DateValue(Date()),[Points],0))/Sum(IIf(Not IsNull([Attempted_Planned]),[Points],0))*100 AS PlannedAttempted,
Sum(IIf(DateValue([Completed_Planned]) < DateValue(Date()]),[Points],0))/Sum(IIf(Not IsNull([Completed_Planned]),[Points],0))*100 AS PlannedCompleted,
Sum(IIf(DateValue([Verified_Planned]) < DateValue(Date()),[Points],0))/Sum(IIf(Not IsNull([Verified_Planned]),[Points],0))*100 AS PlannedClosed


FROM Run INNER JOIN Task ON Run.Run=Task.Group
GROUP BY Run.Test_Case;
HAVING (((Run.Test_Case)=IIf(IsNull([Forms]![Status]![ComboStatusTestCase]),[Test_Case],[Forms]![Status]![ComboStatusTestCase])));

I am sort of confused on where the error could because it just says there is a syntax error. My assumption is that I am using DateValue() in the wrong context. If anyone has any pointers or sees my error please let me know.

Thanks,

 

[ajetrumpet]

Well,

The first thing wrong is the fact that you're using the SUM function on one assigned value from an IIF function. That won't work. You need a range of somekind with the SUM function:

[COLOR="Red"][B][U]You are trying to sum the IIF function's returned value here[/U][/B][/COLOR]

Sum(IIf(DateValue([Attempted_Actual]) < _
   DateValue([Attempted_Actual]),[Points],0)) /

[COLOR="Red"][B][U]Same thing here[/U][/B][/COLOR]

Sum(IIf(Not IsNull([Attempted_Actual]),[Points],0))*100 AS ActualAttempted

If you are trying to sum the entire domain (the column) of the IIF function results, then you need to write another temporary column into the query and use the DSUM() function with reference to the IIF function's temporary column name.

Also, you might consider changing this syntax:

IIF(Not Isnull()

That is VB type syntax. It would be better by switching the placement of the IIF "True/False" results, and just changing the condition to this:

IIF(isnull(

Hopefully this gives some insight for you...

 

[papadega3000]

Adam,

Thanks for your pointers. I understand what your saying and I will try it out and see what works. I know I was able to do this type of sum with the iif that would look at field x and if it was not null it sum every record for in the table. This works so that is why I tried this query. But since the conditional statement is different maybe that has something to do with it. I will use your advice and try it out.

Thanks again.

 

[papadega3000]

In using your pointers I am trying to formulate what the query should look like in terms of using the iif separate then using Dsum(). But I am getting a data mismatch comparison or a error trying to using expression...error.
In Try1 I just wanted to make the expression work but I failed at that.
Try1

SELECT Run.Test_Case, IIf(DateValue([Attempted_Actual])<DateValue([Attempted_Actual]),[Points],0) AS [Total Points]
FROM Run INNER JOIN Task ON Run.Run = Task.Group
GROUP BY Run.Test_Case;

In Try2 I tried moving the syntax around but with no luck here either. At this point it was prompting me for a Parameter value for Tpoints.
Try2

SELECT Run.Test_Case, Sum(DSum([TPoints],[Points])) AS Attempted_Planned
FROM Run INNER JOIN Task ON Run.Run = Task.Group
GROUP BY Run.Test_Case;

I know what I am trying to accompolish but having a hard time implementing it. Any help would be appreciated.

Thanks in advance.