Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Help with Nested If Then Statement in Microsoft Access

Posted on 2007-11-22
Medium Priority
1,877 Views
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
Question by:GMA-CPA

LVL 26

Expert Comment

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

ID: 20334468

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

J
0

LVL 26

Expert Comment

ID: 20334470
0

LVL 14

Accepted Solution

bluelizard earned 500 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

ID: 31410564
THANKS!!!!! HAPPY THANKSGIVING!
0

LVL 58

Expert Comment

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

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

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month12 days, 18 hours left to enroll