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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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:
Upvote 0
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)
 
Upvote 0
Stumac, you still have an unbalanced parenthesis issue!
You have 7 "(" and 9 ")".
I will let you clean your formula up.
 
Upvote 0
: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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Thank you both. The =DSum("Month0_Active","qry_OnTimeDelivery_MetricData") worked perfectly.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top