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