Access: IIf formula missing a bracket, parenthesis...

[gheyman]

I'm getting an error message for this IIF statement. My eyes are not seeing where the problem is.

IIf(CStr(DateDiff("m",[PDIR Due Date],Now()))=0,1,IIf((CStr(DateDiff("m",[PDIR Submission Date],Now()))<0) AND (CStr(DateDiff("m",[PDIR Completed],Now()))>0),1,0)

Thanks!

Found it

IIf(CStr(DateDiff("m",[PDIR Due Date],Now()))=0,1,IIf((CStr(DateDiff("m",[PDIR Submission Date],Now()))<0) AND (CStr(DateDiff("m",[PDIR Completed],Now()))>0),1,0)) the last one

 

[Joe4]

You are missing a right parentheses on the end; always make sure that the number of "(" matches the number of ")".

But what is the point of the CStr function? That converts a number to a string. So, if your formula returned 1, that would change it to "1".
I see no point in using this function in this formula.

 

[stumac]

Agree with Joe, you are using Cstr and then comparing to a number, I would take that out. You can also reduce to one IIF if you switch from AND to OR:

IIF(DateDiff("m",[PDIR Due Date],Now())<>0 OR DateDiff("m",[PDIR Submission Date],Now()))<0 OR DateDiff("m",[PDIR Completed],Now()))>0,0,1)

 

[Joe4]

Stumac, you still have an unbalanced parenthesis issue!
You have 7 "(" and 9 ")".
I will let you clean your formula up.

 

[stumac]

Oops!

IIf(DateDiff("m",[PDIR Due Date],Now())<>0 Or DateDiff("m",[PDIR Submission Date],Now())<0 Or DateDiff("m",[PDIR Completed],Now())>0,0,1)

 

[gheyman]

Joe, If I take out the CStr function does that impact doing a DSum? I am trying to do a DSUM on a form where its looking at the filed with this formula. But the DSUM is not working. I Get a #Name ? result

=DSum([Month0_Active],[qry_OnTimeDelivery_MetricData],'[Month0_Active]="1"')

Here is the Formula in Month0_Active.
MONTH0_Active: IIf(([PDIR Due Date] Is Not Null) And ([PDIR Submission Date] Is Not Null),IIf((DateDiff("m",[PDIR Due Date],Now()))=0,1,IIf(((DateDiff("m",[PDIR Submission Date],Now()))>=0) And ((DateDiff("m",[PDIR Due Date],Now()))<0),1,0)),0)

The formula works no issues with it. I have either a 1 or a 0 in every record in this field - so I am not sure why my DSUM doesn't work.

 

[Joe4]

DSUM sums up a field, so you would want that field to be numeric.
CSTR changes the result FROM a number TO text.
So you would not want to use CSTR on any calculation that you are trying to apply DSUUM to.

 

[stumac]

I am not sure what your Dsum is meant to achieve, you are only filtering out zeros with the month active filter - so it will sum (which is the same as counting in this case) ALL the 1's in the query. You don't need the filter. I suspect you want to filter based on some other field?

try

[COLOR=#333333]=DSum("Month0_Active","qry_OnTimeDelivery_MetricData")[/COLOR]

which will return the same as

[COLOR=#333333]=DSum("Month0_Active","qry_OnTimeDelivery_MetricData"[/COLOR][COLOR=#b22222],"Month0_Active =1"[/COLOR][COLOR=#333333])[/COLOR]

 

[gheyman]

Thank you both. The =DSum("Month0_Active","qry_OnTimeDelivery_MetricData") worked perfectly.

Thanks!