Solved

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

Posted on 2009-05-04
9
229 Views
Last Modified: 2012-05-06
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!
0
Comment
Question by:VBBRett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24295260
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
0
 

Author Comment

by:VBBRett
ID: 24295338
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;
0
 

Author Comment

by:VBBRett
ID: 24295391
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!
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 7

Accepted Solution

by:
coffeeshop earned 500 total points
ID: 24295447
I changed [LimitTaxableWages] to [Taxable-Wages] in the IIf. Don't know if this is right, but the new name should different to the original and I assume that this is the field you want to use. Please check if this is what you want to get.

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',
       [Taxable-Wages], 28900 ) as LimitTaxableWages
FROM AllEmployeeSumoftaxablewages;
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24295455
OK, didn't get your last comment, just a second...
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24295479
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.
0
 

Author Comment

by:VBBRett
ID: 24295663
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.
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24295867
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] )
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24296387
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 ?

0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question