Share via

MS Query - editing SQL and problem with IIF()

Anonymous
2014-06-05T12:59:01+00:00

Hi all:

I am working with MS Query and am editing the SQL statement for the query and have run into a problem. 

Below is the complete SQL statement:

SELECT ArTrnDetail.TrnYear, ArTrnDetail.TrnMonth, ArTrnDetail.ProductClass, SalProductClass.Description, AdmFormData.AlphaValue, InvMaster.StockCode, InvMaster.Description, ArTrnDetail.Invoice, ArTrnDetail.InvoiceDate, ArTrnDetail.Branch, ArTrnDetail.Salesperson, ArTrnDetail.Customer, ArCustomer.Name, ArTrnDetail.Area, ArTrnDetail.QtyInvoiced AS 'Qty', ArTrnDetail.NetSalesValue AS 'Sales Amount', ArTrnDetail.SalesOrder, ArTrnDetail.CostGlCode, ArCustomer.Name +' - '+ ArTrnDetail.Customer AS 'CustNameNo',DatePart(ww, ArTrnDetail.InvoiceDate) as ‘Weekno’,IIF(AdmFormData.AlphaValue = ‘Case’, ArTrnDetail.QtyInvoiced,0) as ‘CaseQty’

FROM SysproCompanyA.dbo.AdmFormData AdmFormData, SysproCompanyA.dbo.ArCustomer ArCustomer, SysproCompanyA.dbo.ArTrnDetail ArTrnDetail, SysproCompanyA.dbo.InvMaster InvMaster, SysproCompanyA.dbo.SalProductClass SalProductClass

WHERE ArTrnDetail.StockCode = InvMaster.StockCode AND InvMaster.StockCode = AdmFormData.KeyField AND ArCustomer.Customer = ArTrnDetail.Customer AND SalProductClass.ProductClass = InvMaster.ProductClass AND ArTrnDetail.Branch = SalProductClass.Branch AND ((AdmFormData.FieldName='SAL001') AND (ArTrnDetail.TrnYear>=DatePart(yyyy,GetDate())-$2))

The error message I get is:  Incorrect syntax near '='.  Statement(s) could not be prepared.  I have "bolded" the part of the statement it is referring to.  I am not sure what he syntax error is.  What I am trying to do is to use the IIF() function to determine the value of the AdmFormData.AlphaValue  field is equal to the text "Case" and if so display the ArTrnDetail.QtyInvoiced in a column/field CaseQty and if not display a zero.

Can anyone tell me what my problem is?  All help is greatly appreciated.

Microsoft 365 and Office
Microsoft 365 and Office

A comprehensive suite of productivity tools and cloud services that enhance collaboration, communication, and efficiency. Combining classic Office apps with advanced Microsoft 365 features, it supports both personal and business needs

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-06-06T07:14:46+00:00

    Hello Darren,

    Since the Excel file in question is involved with SQL query, it would be best if you post your query in the Excel IT Pro forum link mentioned below:

    http://social.technet.microsoft.com/Forums/en-US/excel/threads

    For any other Office related queries, you may always post here and we are happy to assist.

    Thank you.

    Was this answer helpful?

    0 comments