Results 1 to 9 of 9

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

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,665
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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 by gheyman; Sep 16th, 2019 at 10:33 AM. Reason: Found error
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,045
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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 by Joe4; Sep 16th, 2019 at 10:38 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Jul 2010
    Posts
    463
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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)

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,045
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    Stumac, you still have an unbalanced parenthesis issue!
    You have 7 "(" and 9 ")".
    I will let you clean your formula up.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Jul 2010
    Posts
    463
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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



    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)

  6. #6
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,665
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,045
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

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

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    Board Regular
    Join Date
    Jul 2010
    Posts
    463
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    =DSum("Month0_Active","qry_OnTimeDelivery_MetricData")
    which will return the same as

    Code:
    =DSum("Month0_Active","qry_OnTimeDelivery_MetricData","Month0_Active =1")
    Last edited by stumac; Sep 17th, 2019 at 11:26 AM.

  9. #9
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,665
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

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

    Thanks!
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •