Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-05-04
9
Medium Priority
?
233 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 7

Accepted Solution

by:
coffeeshop earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

824 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