asked on

I have a new query expression that I need to be done

I have an IIf statement that I am trying to do. My expression that I am trying to do is if the DED Code is equal to TXFE or

IIf((AllEmployeeSumoftaxablewages.[DED Code] = 'TXFE' or (AllEmployeeSumoftaxablewages.[DED Code] = 'TXUN'),[LimitTaxableWages] = 28900,0) as LimitTaxableWages

I get a syntax error in my SQL statement and LimitTaxableWages is a new field I am trying to create in this query. Please let me know. Thanks!

coffeeshop

There is a bracket missing, and the value should be used as follow (if I understand what you want to do - maybe I did not get it at all):

IIf((AllEmployeeSumoftaxablewages.[DED Code] = 'TXFE' or (AllEmployeeSumoftaxablewages.[DED Code] = 'TXUN'),[LimitTaxableWages], 28900)) as LimitTaxableWages

VBBRett

ASKER

I have a syntax error with a comma somewhere. Here is my complete query with your expression at the bottom:

SELECT AllEmployeeSumoftaxablewages.[Record Type], AllEmployeeSumoftaxablewages.Company, AllEmployeeSumoftaxablewages.Employee, AllEmployeeSumoftaxablewages.[Process level], AllEmployeeSumoftaxablewages.Dept, AllEmployeeSumoftaxablewages.[Check ID], AllEmployeeSumoftaxablewages.[Pay Code], AllEmployeeSumoftaxablewages.[DED Code], AllEmployeeSumoftaxablewages.[TR-Date], AllEmployeeSumoftaxablewages.[Check Date], AllEmployeeSumoftaxablewages.Hours, AllEmployeeSumoftaxablewages.[Wage-Amt], AllEmployeeSumoftaxablewages.[Ded-Amt], AllEmployeeSumoftaxablewages.[Taxable-Wages], AllEmployeeSumoftaxablewages.Excess, AllEmployeeSumoftaxablewages.[Job Code], AllEmployeeSumoftaxablewages.[Work State], AllEmployeeSumoftaxablewages.[Ded End Date], AllEmployeeSumoftaxablewages.[Check Number], AllEmployeeSumoftaxablewages.[Case Number], AllEmployeeSumoftaxablewages.[File Number], AllEmployeeSumoftaxablewages.EDM, AllEmployeeSumoftaxablewages.Expr4, AllEmployeeSumoftaxablewages.[SumOfTaxable Wages],
IIf((AllEmployeeSumoftaxablewages.[DED Code] = 'TXFE' or (AllEmployeeSumoftaxablewages.[DED Code] = 'TXUN'),[LimitTaxableWages], 28900)) as LimitTaxableWages
FROM AllEmployeeSumoftaxablewages;

VBBRett

ASKER

What I really want it to do is have an iff statement like the following:

If the DED Code = TXFE or TXUN then LimitTaxableWages = 28900
If the DED Code = TXSC or TXSE then LimitTaxableWages = 106800

Please help me figure this expression out. Thank you!

ASKER CERTIFIED SOLUTION

coffeeshop

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

coffeeshop

OK, didn't get your last comment, just a second...

coffeeshop

IIf ( AllEmployeeSumoftaxablewages.[DED Code] = 'TXFE' or
AllEmployeeSumoftaxablewages.[DED Code] = 'TXUN', 28900,
IIf ( AllEmployeeSumoftaxablewages.[DED Code] = 'TXSC' or
AllEmployeeSumoftaxablewages.[DED Code] = 'TXSE', 106800,
[Taxable-Wages] )) as LimitTaxableWages

Please check if [Taxable-Wages] is what you want to have if nothing of the DED Codes match.

VBBRett

ASKER

OK, so how do you do a check to see if there is anything in a record?

What I am trying to do is if there is nothing in the AllEmployeeSumoftaxablewages.[SumOfTaxable Wages] record column, I want to insert into the SumOfTaxableWages whatever is in the AllEmployeeSumoftaxablewages.[Taxable-Wages] colunn. How would I do an iif statement doing that? Does that make sense? Thank you very much on your help so far.

coffeeshop

Depending on your underlaying query "AllEmployeeSumoftaxablewages" the value for [SumOfTaxable Wages] could be NULL, 0 or <>0. I assume that the last two gives you valid information back, so I check only with "IsNull" in the IIf-Statement. The following return the sum, if not NULL:

IIf ( IsNull(AllEmployeeSumoftaxablewages.[SumOfTaxable Wages]),
AllEmployeeSumoftaxablewages.[Taxable-Wages],
AllEmployeeSumoftaxablewages.[SumOfTaxable Wages] )

This returns the sum if <>0 and not NULL:

IIf ( AllEmployeeSumoftaxablewages.[SumOfTaxable Wages]<>0,
AllEmployeeSumoftaxablewages.[SumOfTaxable Wages],
AllEmployeeSumoftaxablewages.[Taxable-Wages] )

Mark Wills

So, if there is something in [SumOfTaxable Wages] use that, other wise use [taxable-wages]

ie IIf ( Nz(AllEmployeeSumoftaxablewages.[SumOfTaxable Wages],0) <> 0
AllEmployeeSumoftaxablewages.[SumOfTaxable Wages],
AllEmployeeSumoftaxablewages.[Taxable-Wages] )

what does that have to do with :

If the DED Code = TXFE or TXUN then LimitTaxableWages = 28900, or, If the DED Code = TXSC or TXSE then LimitTaxableWages = 106800

ie IIf ( AllEmployeeSumoftaxablewages.[DED Code] = 'TXFE' or AllEmployeeSumoftaxablewages.[DED Code] = 'TXUN', 28900,
IIf ( AllEmployeeSumoftaxablewages.[DED Code] = 'TXSC' or AllEmployeeSumoftaxablewages.[DED Code] = 'TXSE', 106800,
0)) as LimitTaxableWages

or are they two seperate questions ?