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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Parameters and If Statement in MS Query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

15 Posts

Posted - 08/06/2010 :  12:21:20  Show Profile  Reply with Quote
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 - 08/06/2010 :  13:15:00  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000