Solved

Help with Nested If Then Statement in Microsoft Access

Posted on 2007-11-22
7
1,860 Views
Last Modified: 2013-11-27
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
Comment
Question by:GMA-CPA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 20334457
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 20334468
how about

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

J
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 20334470
month(dateadd("m",2,dateserial(right(jrnldate,4), mid(jrnldate, 5,2), left(jrnldate,2))))
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 14

Accepted Solution

by:
bluelizard earned 125 total points
ID: 20334473
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
 

Author Closing Comment

by:GMA-CPA
ID: 31410564
THANKS!!!!! HAPPY THANKSGIVING!
0
 
LVL 58
ID: 20334561
 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
 
LVL 2

Expert Comment

by:geekspeaknow
ID: 20334577
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question