Make a negative number a "0" (2 Viewers)

[dullster]

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: IIf([tblEmployees].[Marital Status]=1 And [Basic Salary]<25727,[Basic Salary]-[Amount from Column A]-[Exemption Credit],IIf([tblEmployees].[Marital Status]=2 And [Basic Salary]<17780,[Basic Salary]-[Amount from Column A])-([Exemption Credit]))

 

[GaP42]

So if the (expression result) is < 0, then 0, else ....
Apply the logic, syntax of the iif statement.

 

[plog]

Ha ha, still not solved, still trying it the hard way:

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

Even before this new wrinkle everyone advised a custom function. This additional logic now screams for one. Your logic is just too complex to cram into one line. You need to write a custom function to generate the results you want.

 

[arnelgp]

Net Wage: Switch(IIf([tblEmployees].[Marital Status]=1 And [Basic Salary]<25727,[Basic Salary]-[Amount from Column A]-[Exemption Credit],IIf([tblEmployees].[Marital Status]=2 And [Basic Salary]<17780,[Basic Salary]-[Amount from Column A])-([Exemption Credit])) < 0, 0 True, IIf([tblEmployees].[Marital Status]=1 And [Basic Salary]<25727,[Basic Salary]-[Amount from Column A]-[Exemption Credit],IIf([tblEmployees].[Marital Status]=2 And [Basic Salary]<17780,[Basic Salary]-[Amount from Column A])-([Exemption Credit])))

 

[dullster]

Thank you, I was starting to read about "switch". Never heard of it before. It's a new learning curve for me, bur it sounds like once you learn it, it's easier. I will review this tomorrow.

 

[dullster]

Ha ha, still not solved, still trying it the hard way:

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

Even before this new wrinkle everyone advised a custom function. This additional logic now screams for one. Your logic is just too complex to cram into one line. You need to write a custom function to generate the results you want.

Actually, I'm breaking it down into smaller expressions to adjust to the many scenarios. I'm not experienced in function, and had hoped I could learn, but it doesn't appear your that teacher. I hope you can learn through all of this there is more than one way to get a DB to work.

 

[plog]

More like a guidance counselor than teacher. Your teacher was MajP in the other thread. Check out post #20 there

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]

from CoPilot:

When to Use Which?

• IIF(): Use when you have a single condition and two possible outcomes.
  
  
• SWITCH(): Use when you have multiple conditions and want to return a different value for each matching condition.

 

[dullster]

from CoPilot:

When to Use Which?

• IIF(): Use when you have a single condition and two possible outcomes.
  
  
• SWITCH(): Use when you have multiple conditions and want to return a different value for each matching condition.

Thank you.

I started with Access about 35 years ago when it moved from DOS to Windows.

I've built several and maintained several since then. This DB that I inherited, was created in the DOS program specifically for Towns in Wisconsin, US. It provided the data that is specific to the State of Wisconsin reports, which affects the State Revenue provided to the Townships by possibly a $50,000 or more per year.. It was almost ready to become obsolete when I acquired it. It does a great job of supplying the information for the Wisconsin report but became very limited to only that. It needed to expand or it was going to die. Due to its ability to complete those reports I can't change the background or don't want to too much but I can build on the front ground to make it a better program and work for what they need today, 40 years later. I appreciate all the help I can get because it's a very important program to the Townd in Wisconsin. It would have been easier to create a new DB but the fact that the backend is great, is why I'm trying hard to salvage it.

I hope this explains the issues I'm requesting help on.

 

[dullster]

More like a guidance counselor than teacher. Your teacher was MajP in the other thread. Check out post #20 there

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

from CoPilot:

When to Use Which?

• IIF(): Use when you have a single condition and two possible outcomes.
  
  
• SWITCH(): Use when you have multiple conditions and want to return a different value for each matching condition.

Thank you.

I started with Access about 35 years ago when it moved from DOS to Windows.

I've built several and maintained several since then. This DB that I inherited, was created in the DOS program specifically for Towns in Wisconsin, US. It provided the data that is specific to the State of Wisconsin reports, which affects the State Revenue provided to the Townships by possibly a $50,000 or more per year.. It was almost ready to become obsolete when I acquired it. It does a great job of supplying the information for the Wisconsin report but became very limited to only that. It needed to expand or it was going to die. Due to its ability to complete those reports I can't change the background or don't want to too much but I can build on the front ground to make it a better program and work for what they need today, 40 years later. I appreciate all the help I can get because it's a very important program to the Townd in Wisconsin. It would have been easier to create a new DB but the fact that the backend is great, is why I'm trying hard to salvage it.

I hope this explains the issues I'm requesting help on.

 

[Gasman]

Well if you started with Access 35 years ago, a simple UDF should be easy for you?
Certainly it is easier to construct, follow and amend?
But no, you want to chase down the rabbit hole.
The UDF is basically what you described in your long other thread. You would write it out in code as you did in English.

I would have created a UDF as I showed you in the other thread. Similar complicated logic.

 

[DHookom]

Apparently, another opinion was needed to reinforce the consensus here.

 

[Gasman]

Multiple crossposter without notification.
Say hello to my IL.

 

[Pat Hartman]

Apparently, another opinion was needed to reinforce the consensus here.

Maybe another voice from the actual experts will convince the poster to create a function instead. No one wants to have to parse that complicated expression in SQL.

@dullster At some point, after 35 years you might want to consider learning VBA. It is far superior to macros and although queries are generally more efficient than code loops, don't make your head spin with complicated expressions that are difficult to enhance embedded in them

In VBA you would use a Select Case to simplify your nested logic and you would need to pass in the table columns as arguments for the function to use. Give it a shot and we'll help you finish it.

Like If's and IIf's, Select Case's can also be nested.