Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Parameters and If Statement in MS Query

Author  Topic 

Starting Member

15 Posts

Posted - 2010-08-06 : 12:21:20
I have a table in our SQL Database that I want to query in Excel 2003 using MS Query. I am receiving errors. Here is the basic SQL Script without the Parameters I need.

SELECT Rprt_L12MonOrdOHByItem.ItmAnalyst, Rprt_L12MonOrdOHByItem.IntbMsdsCode, Rprt_L12MonOrdOHByItem.IntbCommGrup, Rprt_L12MonOrdOHByItem.IntbGrup, Rprt_L12MonOrdOHByItem.IntbUserCode1, Rprt_L12MonOrdOHByItem.ItmStat, Rprt_L12MonOrdOHByItem.ItemID, Rprt_L12MonOrdOHByItem.InitDesc1, Rprt_L12MonOrdOHByItem.InitDesc2, Rprt_L12MonOrdOHByItem.InitRevision, Rprt_L12MonOrdOHByItem.InitOuterPackQty, Rprt_L12MonOrdOHByItem.InitInnerPackQty, Rprt_L12MonOrdOHByItem.InitQtyPerCase, Rprt_L12MonOrdOHByItem.InitPricBase, Rprt_L12MonOrdOHByItem.ItmQOH, Rprt_L12MonOrdOHByItem.ItmQOHCost, Rprt_L12MonOrdOHByItem.ItmQOHDoll, Rprt_L12MonOrdOHByItem.SOShippedTot, Rprt_L12MonOrdOHByItem.SOShpCostTot, Rprt_L12MonOrdOHByItem.SOShpDollarsTot, Rprt_L12MonOrdOHByItem.LastUpdate
FROM gartner.dbo.Rprt_L12MonOrdOHByItem Rprt_L12MonOrdOHByItem

I want to add a Parameter to [ItmAnalyst] but if the parameter is left blank I want it to return all records.
I have tried; IIf([Parameter] is null, '%',[Parameter]) but get a syntax error.
I have also tried; Like '%' & [Parameter] but it says nvarchar and varchar are incompatible in '&' Operation.
So Then I tried; Like '%' & cast([Parameter] as nvarchar(50)) but got a syntax error
So Then I tried; Like '%' & cast([Parameter] as nvarchar) but got a syntax error.

This seems like a simple Idea to me but I can't figure out how to make it work. Please help if you can. If this question is not in the right area please tell me where I should post it. Thanks.

Not Just a Number

15586 Posts

Posted - 2010-08-06 : 13:15:00
WHERE Col = IIF([Parameter] IS NULL, Col, [Parameter])


Maybe you need to visit a VBA Site



Hint: Want your questions answered fast? Follow the direction in this link

Add yourself!

Go to Top of Page

- Advertisement -