Solved

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

Posted on 2009-05-04
9
209 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL - Rotating Values in SQL 9 65
convert in derived column 7 28
SQL Server Insert where not exists 24 41
sql server concatenate fields 10 33
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 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