• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1887
  • Last Modified:

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?
0
GMA-CPA
Asked:
GMA-CPA
1 Solution
 
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
 
jerryb30Commented:
month(dateadd("m",2,dateserial(right(jrnldate,4), mid(jrnldate, 5,2), left(jrnldate,2))))
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bluelizardCommented:
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
 
GMA-CPAAuthor Commented:
THANKS!!!!! HAPPY THANKSGIVING!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now