Solved

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

Posted on 2009-05-04
9
190 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now