Solved

Help with Nested If Then Statement in Microsoft Access

Posted on 2007-11-22
7
1,854 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
Technology Partners: 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!

 
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 57
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

739 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