IIf() function in access

[anitaschramm]

I am trying to create an if statement to calculate tax withholdings for federal and state taxes. Can you do an if else statement in Access? I am a bit rusty.

Thanks for your help.

[missinglinq]

Your title states "I need help with if statement in access" but in point of fact, the only question you pose is "Can you do an if else statement in Access?"

The answer is "Yes," but if you need help using the If...End If construct, you'll have to give us more information to work with than you have so far.

Welcome to Bytes!

Linq ;0)>

[anitaschramm]

Any suggestions to calculating payroll tax in Access query would be appreciated

Ok. In my previous message, I stated I am trying to do a database to calculate payroll deductions. I am trying to do this in a query. The query so far goes like this: Federal: (IIf([gross] Between 233 And 250,"1") Or IIf([gross]<233,"0") Or IIf([gross] Between 250 And 260,"2") Or IIf([gross] Between 260 And 270,"3") Or IIf([gross] Between 270 And 280,"4") Or IIf([gross] Between 280 And 290,"5") Or IIf([gross] Between 290 And 300,"6") Or IIf([gross] Between 300 And 310,"7") Or IIf([gross] Between 310 And 320,"8") Or IIf([gross] Between 320 And 330,"9") Or IIf([gross] Between 330 And 340,"10") Or IIf([gross] Between 340 And 350,"11") Or IIf([gross] Between 740 And 760,"69") Or IIf([gross] Between 760 And 780,"72"))

My query runs, but comes up with a -1 as the amount for federal tax withheld, which is not correct. Is my syntax correct? Do I need to put this in a module? I'm pretty much lost. Thanks again for your help.

[orangeCat]

Your syntax is an issue.
Here is a link to Microsoft showing iif syntax and examples.

http://office.microsoft.com/en-us/access/HA012288531033.aspx

Good luck.

[topher23]

OrangeCat has a point about your syntax. You are using "Or" where you are looking for an Else. IIf has a built in Else, and you can nest IIf's, like so:

IIf(criteria, true-statement,
IIf(criteria, true-statement,
IIf(criteria, truestatement, falsestatement)))

Just remember to close all of your IIf's at the end with the proper number of close-parentheses and your function will work fine.

In your particular case, I'm not sure if that many IIf's will nest, but it's worth a try.

Also, I've never had much luck using Between in an IIf function. I tend to use this method, which is more kludgy but hasn't failed me.

IIf([gross]>232 And [gross]<251,"1")

[missinglinq]

In point of fact, IIf() was intended to simplify

If [I]xxxx[/I] Then

Else
 [I]xxxx[/I]

End If

into as single line. It was never intended to be nested a gazillion times into a into an unreadible mish-mash! Nesting more than once or twice frequently leads to problems, and is always a terrible idea, for the simple reason that it makes following the logic all but impossible.

You'd be much better off here using Select Case for this type of thing; easier to read, easier to maintain.

Linq ;0)>