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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,690
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
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
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.
 
Last edited:

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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:

Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
Stumac, you still have an unbalanced parenthesis issue!
You have 7 "(" and 9 ")".
I will let you clean your formula up.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
:eek::eek:

Oops!

Code:
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

Well-known Member
Joined
Nov 14, 2005
Messages
1,690
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
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

Active Member
Joined
Jul 16, 2010
Messages
471
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

Code:
[COLOR=#333333]=DSum("Month0_Active","qry_OnTimeDelivery_MetricData")[/COLOR]
which will return the same as

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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,690
Thank you both. The =DSum("Month0_Active","qry_OnTimeDelivery_MetricData") worked perfectly.

Thanks!
 

Forum statistics

Threads
1,085,167
Messages
5,382,089
Members
401,768
Latest member
katana_flyer

Some videos you may like

This Week's Hot Topics

Top