Nesting IIF Statement with functions that end in (field)

[dullster]

I have 4 IIF Statement that calculate State Taxes for marital status: Married, Single and greater than > income, or less than <income for each marital status.

All 4 Statements end in -([Exemption credit]) or -([Exemption credit])). They work individually they way they are supposed to.

When I combine just 2 of them to start, (working my way out) I get “The expression you entered contains invalid syntax, or you need to enclose your text data in quotes.” It highlights the comma that separates the expressions. I could be wrong but, I don't think it likes my expression ending in -([field]). I can’t remove the () around [field] because it changes the results completely.

I’ve attached the DB to show the results I have in QryStateTax. I need to combine MarSM, MarLg, SingSM and SingLG into one column and carry those results to the qryCDpayrolltaxesWI field currently name Expr1. qryCDpayrolltaxesWI will calculate the exact State Tax to export the results to the QryCDpaytollrecomm which combines all the Medicare, Social Security, Federal tax and State tax that will show the recommended taxes for payroll.

MarSM2: IIf([tblEmployees].[Marital Status]=1 And [Basic Salary]<25727,[tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Amount from Column A]-([Exemption credit]))

Changes the negatives from above to 0
MarSM: IIf([MarSM2]<0,0,[MarSM2])

MarLG: IIf([tblEmployees].[Marital Status]=1 And [Basic Salary]>25727,[tblEmployees].[Basic Salary]-([tblpayrolltaxes].[Amount from Column A]-([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower])*0.2))-([Exemption credit])

SingSM1: IIf([tblEmployees].[Marital Status]=2 And [Basic Salary]<17780,[tblEmployees].[Basic Salary]-([tblpayrolltaxes].[Amount from Column A]-[tblpayrolltaxes].[Lower])-([Exemption credit]))

Changes the negative from above to 0
SingSM: IIf([SingSM1]<0,0,[SingSM1])

SingLG: IIf([tblEmployees].[Marital Status]=2 And [Basic Salary]>17780,[tblEmployees].[Basic Salary]-([tblpayrolltaxes].[Amount from Column A]-([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower])*0.12))-([Exemption credit])

I’m sure this could all be done in a vba easily, which I plan to learn which can be Ver2 of the DB, but right now I need to get this to testers to make sure it works for them.

 

[plog]

Oh, this will be the time we just shut up about the right way and just give you what you want:

Make a negative number a "0"

I have a query expression with some of the results being a negative numbers that I wish to change to a 0. I have Salaries minus several deductions created several "Employees" to be negative which means they get a standard deduction so I want the results to be 0 instead of negative. Net Wage...

www.access-programmers.co.uk

Solved - Combining IIf Statements

I have 2 IIF statements that work individually, when I combine them I get this error, The expression you entered has a function containing the wrong number of arguments. What am i doing wrong? IIf([tblEmployees].[Marital Status]=1,[tblEmployees].[Basic...

www.access-programmers.co.uk

This explanation won't help, but its a new thought at any rate:

We are not any better at making syntax errors than you. We avoid them by not cramming all our logic into one massive line of code. We use VBA not to show off, but because we know if we have a huge logical problem to work out, when we divide it up and work line by line we are less likely to make those syntatical errors and when we do the IDE shows us which line of those many it is. We still make the errors you make (missing a comma, not enough arguments, parenthesis missing) but by using VBA it helps us identify them and fix them quickly.

We suggest VBA because we know that throwing a ton of logic into 1 line is beyond our capabilities. This is not going to take you a month to do in VBA. Had you started 2 days after we initially suggested it you would have the solution you needed by now. But, keep fighting the good fight and call me the A-hole.

 

[dullster]

Oh, this will be the time we just shut up about the right way and just give you what you want:

Make a negative number a "0"

I have a query expression with some of the results being a negative numbers that I wish to change to a 0. I have Salaries minus several deductions created several "Employees" to be negative which means they get a standard deduction so I want the results to be 0 instead of negative. Net Wage...

www.access-programmers.co.uk

Solved - Combining IIf Statements

I have 2 IIF statements that work individually, when I combine them I get this error, The expression you entered has a function containing the wrong number of arguments. What am i doing wrong? IIf([tblEmployees].[Marital Status]=1,[tblEmployees].[Basic...

www.access-programmers.co.uk

This explanation won't help, but its a new thought at any rate:

We are not any better at making syntax errors than you. We avoid them by not cramming all our logic into one massive line of code. We use VBA not to show off, but because we know if we have a huge logical problem to work out, when we divide it up and work line by line we are less likely to make those syntatical errors and when we do the IDE shows us which line of those many it is. We still make the errors you make (missing a comma, not enough arguments, parenthesis missing) but by using VBA it helps us identify them and fix them quickly.

We suggest VBA because we know that throwing a ton of logic into 1 line is beyond our capabilities. This is not going to take you a month to do in VBA. Had you started 2 days after we initially suggested it you would have the solution you needed by now. But, keep fighting the good fight and call me the A-hole.

I am just starting my first VBA. Actually I think i could have had it done in less than 2 day after I started. I still trying to work through what I did incorrectly because it won't load into the query yet, but it's a work in progress. I posted what I did so see if anyone could help if you want to take a look and give me input.

 

[Steve R.]

Since I no longer work with MS Access, I cannot provide an MS Access solution. I would suggest using the CASE statement, it is easier method.

Select Case statement (VBA)

Office VBA reference topic

learn.microsoft.com

 

[arnelgp]

i think there is no advantage of using a Function on your case.
see BasTaxCompute module and query QryStateTax_arnelgp that uses ComputeTax function.

 

[dullster]

i think there is no advantage of using a Function on your case.
see BasTaxCompute module and query QryStateTax_arnelgp that uses ComputeTax function.

I didn't know there was a BasTaxCompute module or QryStateTax_arnelgp. Where are these?

 

[arnelgp]

you open the the query in design view and see the Expressions on the Computed columns of MarSM2, MarSM, MarLG, etc.

 

[arnelgp]

must have uploaded the wrong version.
here again.

 

[dullster]

i think there is no advantage of using a Function on your case.
see BasTaxCompute module and query QryStateTax_arnelgp that uses ComputeTax function.

didn't know you added to my DB. I'll look at it now.

 

[dullster]

must have uploaded the wrong version.
here again.

you open the the query in design view and see the Expressions on the Computed columns of MarSM2, MarSM, MarLG, etc.
View attachment 119617
View attachment 119618

That is doing what I had it doing. I need them to total all in the same column.

 

[arnelgp]

on query qryCDpayrolltaxesWI, you are only processing Single status, for Married what formula do you use?
so after combining all of these into 1 function, you have no need for query qryStateTax?

 

[dullster]

on query qryCDpayrolltaxesWI, you are only processing Single status, for Married what formula do you use?
so after combining all of these into 1 function, you have no need for query qryStateTax?

I still need the qryTaxState. I needed it to calculate all the taxes to one column. Thus why I wanted help combining the IIf statements. I had to learn how to do a vba and calculate them all in that. Thank you for your help.

 

[dullster]

on query qryCDpayrolltaxesWI, you are only processing Single status, for Married what formula do you use?
so after combining all of these into 1 function, you have no need for query qryStateTax?

Solved - Combining IIf Statements

I have 2 IIF statements that work individually, when I combine them I get this error, The expression you entered has a function containing the wrong number of arguments. What am i doing wrong? IIf([tblEmployees].[Marital Status]=1,[tblEmployees].[Basic...

www.access-programmers.co.uk

 

[arnelgp]

if it is alreay Solved on that thread, stick to that thread.