Make a negative number a "0"

[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.

 

[dullster]

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.

I agree. It's time. How do I start? Do I need all my IIf's that work put together first? I'm not sure where to start.

 

[Pat Hartman]

I gave you several hints earlier and asked you to give it a try. We are happy to help but we shouldn't just do it for you. For your own good, you need to make a start.

I suggest you start by converting the IIf() to an If-Then-Else format on paper. Be conscious of properly indenting the code as you write it or you'll get lost. Post the VBA "If" once you're done. Or just continue to clean it up a little by changing the outer If to a Select Case. This really isn't difficult once you have the IIf() working to convert it to a normal VBA structure which is easier to modify. Technically, you could simply use the IIf() in the function and leave it at that since the IIf() is perfectly valid VBA. The reason to convert is for readability and changeability. Unless you have a loop that processes hundreds of thousands of records at a go, strive for readability rather than efficiency.

The function header needs the variable fields to be passed in. But once you get the If built, we can clean up what you have if you really still need the help. I know you can do this

 

[dullster]

I gave you several hints earlier and asked you to give it a try. We are happy to help but we shouldn't just do it for you. For your own good, you need to make a start.

I suggest you start by converting the IIf() to an If-Then-Else format on paper. Be conscious of properly indenting the code as you write it or you'll get lost. Post the VBA "If" once you're done. Or just continue to clean it up a little by changing the outer If to a Select Case. This really isn't difficult once you have the IIf() working to convert it to a normal VBA structure which is easier to modify. Technically, you could simply use the IIf() in the function and leave it at that since the IIf() is perfectly valid VBA. The reason to convert is for readability and changeability. Unless you have a loop that processes hundreds of thousands of records at a go, strive for readability rather than efficiency.

The function header needs the variable fields to be passed in. But once you get the If built, we can clean up what you have if you really still need the help. I know you can do this

Thank you. People say it's easier but I'm scared to start as it is foreign ground but I will give it try and come back. Thank you.

 

[DHookom]

Converting on paper could look like a process flow diagram. There are true/false questions that direct the next process/decision. As you need a new input to the process, it comes from a new argument in your function.

 

[Pat Hartman]

Thank you. People say it's easier but I'm scared to start as it is foreign ground but I will give it try and come back. Thank you.

Remember the IIf() is
IIf(condition, true path, false path)

And either or both the true path or the else path can be nested. This is why you need to indent properly so you'll see what path you are on.

IIf(condition, truepath IIf(condition2, true path, false path), falsepath IIf(condition3, true path, false path))

Once you start nesting, the IIf() gets extremely hard to read. And to change it, you have to decode it and get it clear in your mind. By virtue of the structure of the If-then-else, assuming you indent correctly, it is almost trivial to read and modify.

If condition then
    truepath If condition2 then
        true path
    else
        false path
    end if
Else
    falsepath If condition3 then
        true path
    else
        false path
    end if
end if

 

[DHookom]

Here's how I would go about converting the expression into a VBA function in a standard module named modBusinessCalcs. You can test this in the immediate window by using:
? YourFunctionName(value1, value2, value3,...)

You should also understand how to add a breakpoint to allow your to step through your code line by line checking the values as you go.

Public Function GetNetWage(intMarStatus As Integer, dblBasSal As Double, dblAmtColA As Double, _
        dblExCred As Double) As Double
'trial expression copied and pasted into module and then simplified by removing table name
'   each 'phrase or part of the expression is place on its own line
'   all inputs are identified and entered into the arguments for the function on the first line.
'       data types are a guess but should be corrected or set to variant of possibility of Null
'  Also explain what the arguments represent
'  Enter the date and your initials up here
'Net Wage: IIf([Marital Status]=1 And [Basic Salary]<25727, _
               [Basic Salary]-[Amount from Column A]-[Exemption Credit], _
               IIf([Marital Status]=2 And [Basic Salary]<17780, _
                   [Basic Salary]-[Amount from Column A])-[Exemption Credit])
                   ' missing a comma in the line above I think
                 
    Dim lngBasSalHigh As Long, lngBasSalMed As Long
    lngBasSalHigh = 25727    'this belongs near top of the function where it's easy to find
    lngBasSalMed = 17780     'there should be no values like this below here
   
    Select Case intMarStatus
        Case 1   'married
            If dblBasSal < lngBasSalHigh Then
                GetNetWage = dblBasSal - dblAmtColA - dblExCred
            End If
        Case 2   'single
            If dblBasSal < lngBasSalMed Then
                GetNetWage = dblBasSal - dblAmtColA - dblExCred
            End If
        Case Else
            ' return something if no qualification above
           
    End Select

End Function