Query expression negative to a 0

[ChErD]

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]))

 

[dhookom]

Create a small user-defined function that accepts the field values as arguments and returns the appropriate value. You can encapsulate this logic to use anywhere in your application and include comments in the code. It’s easier to debug than a complex expression. When the 25,727 changes, just edit your modBusinessCalcs module and note the change in the comments.

 

[strongm]

A use of Format in the query might work here. But, as dhookom says, probably best to look at a UDF

 

[mikrom]

If you want to use IIFs then write first with normal IF - ELSE what result you want to get and then transform it into nested IIFs,

For example, if you want something like this

If [tblEmployees].[Marital Status]=1 Then 
   If [Basic Salary]>0 And [Basic Salary]<25727 Then
      result = [Basic Salary]-[Amount from Column A]-[Exemption Credit]
   Else 
      result = 0
   End If
Else   
   If [tblEmployees].[Marital Status]=2 Then 
      If [Basic Salary]>0 And [Basic Salary]<17780 Then 
         result = [Basic Salary]-[Amount from Column A]-[Exemption Credit]
      Else
         result = 0
      End If
End If

then this should be it using nested IIFs

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

 

[strongm]

Format([Basic Salary]-[Amount from Column A]-[Exemption Credit],"#.0#;\0")

 

[combo]

I agree that UDF is the best tool to structure conditions. For simple calculations I frequently use:

Function Max2(Val1 As Double, Val2 As Double) As Double
    If Val1 > Val2 Then Max2 = Val1 Else Max2 = Val2
End Function

I would also check if your formula is proper. In:

IIf(
    [tblEmployees].[Marital Status]=1 And [Basic Salary]<25727,     {condition1}
    [Basic Salary]-[Amount from Column A]-[Exemption Credit],       {value if condition1=True}
    IIf([tblEmployees].[Marital Status]=2 And [Basic Salary]<17780, {condition1=False, condition2}
        [Basic Salary]-[Amount from Column A]                       {value if condition1=False and condition2=True}
    )
    -([Exemption Credit])                                           {value if value if condition1=False and condition2=False}
)

some conditions are not handled, in this case you end up with -([Exemption Credit]).

 

[dhookom]

This question is cross posted in Access World Forums and the suggestions there are much the same as here The OP has already been informed about the issues we have with cross posting so no need to comment about it. The threads in the other forum are up to 79 posts of advice to use VBA.