Help with Nested If Then Statement in Microsoft Access

Can someone help me with correcting the syntex in this attached nested if-then statement for Access?

IIf(Mid([JRNLDATE],5,2)="11","01",
IIf(Mid([JRNLDATE],5,2)="12","02"),
IIf(Mid([JRNLDATE],5,2)="01","03"),
IIf(Mid([JRNLDATE],5,2)="02","04)",
IIf(Mid([JRNLDATE],5,2)="03","05"),
IIf(Mid([JRNLDATE],5,2)="04","06"),
IIf(Mid([JRNLDATE],5,2)="05","07"),
IIf(Mid([JRNLDATE],5,2)="06","08"),
IIf(Mid([JRNLDATE],5,2)="07","09"),
IIf(Mid([JRNLDATE],5,2)="08","10"),
IIf(Mid([JRNLDATE],5,2)="09","11"),
IIf(Mid([JRNLDATE],5,2)="10","12",
"ERROR" ))

It's telling me it has the wrong number of arguments.  I'm sure it's my syntex because of the nested if-then statements.  There must be an easier way to do this in access but if this can work it's fine....OH GREAT SYNTAX TECH WHERE ARE YOU?
GMA-CPAAsked:
Who is Participating?
 
bluelizardConnect With a Mentor Commented:
try this (might need to bring it back to 1 line; the formatting is just for better understanding):

IIf(Mid([JRNLDATE],5,2)="11",
    "01",
    IIf(Mid([JRNLDATE],5,2)="12"
        ,"02",
        IIf(Mid([JRNLDATE],5,2)="01",
            "03",
            IIf(Mid([JRNLDATE],5,2)="02",
                "04",
                IIf(Mid([JRNLDATE],5,2)="03",
                    "05",
                    IIf(Mid([JRNLDATE],5,2)="04",
                        "06",
                        IIf(Mid([JRNLDATE],5,2)="05",
                            "07",
                            IIf(Mid([JRNLDATE],5,2)="06",
                                "08",
                                IIf(Mid([JRNLDATE],5,2)="07",
                                    "09",
                                    IIf(Mid([JRNLDATE],5,2)="08",
                                        "10",
                                        IIf(Mid([JRNLDATE],5,2)="09",
                                            "11",
                                            IIf(Mid([JRNLDATE],5,2)="10",
                                                "12",
                                                "ERROR"
                                               )
                                           )
                                       )
                                   )
                               )
                           )
                       )
                   )
               )
           )
       )
   )



--bluelizard
0
 
jerryb30Commented:
You're trying to add 2 months to your date field. You do not have enough parens at the end, first of all.
But why not add 2 months to your date,and extract the month from that?
What does jrnldate look like?
0
 
jefftwilleyCommented:
how about

iif(mid([jjrnldate],5,2)>10,mid([jjrnldate],5,2)-10,mid([jjrnldate],5,2)+2)

J
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
jerryb30Commented:
month(dateadd("m",2,dateserial(right(jrnldate,4), mid(jrnldate, 5,2), left(jrnldate,2))))
0
 
GMA-CPAAuthor Commented:
THANKS!!!!! HAPPY THANKSGIVING!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
 As a general rule, you really don't want to use IIF() for anything more then a couple of nested conditional functions.  Instead, write the logic out in a function and call that.

  It will be much easier to read, understand, debug, and maintain in the future.

JimD
0
 
geekspeaknowCommented:
Here you go:

IIf(Mid([JRNLDATE],5,2)="01","03",IIf(Mid([JRNLDATE],5,2)="02","04",IIf(Mid([JRNLDATE],5,2)="03","05",IIf(Mid([JRNLDATE],5,2)="04","06",IIf(Mid([JRNLDATE],5,2)="05","07",IIf(Mid([JRNLDATE],5,2)="06","08",IIf(Mid([JRNLDATE],5,2)="07","09",IIf(Mid([JRNLDATE],5,2)="08","10",IIf(Mid([JRNLDATE],5,2)="09","11",IIf(Mid([JRNLDATE],5,2)="10","12",IIf(Mid([JRNLDATE],5,2)="11","01",IIf(Mid([JRNLDATE],5,2)="12","02",""))))))))))))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.