Parameter is missing a value

Hi Folks,

I'm receiving the "Parameter is missing a value" error message while testing my report.

I have five parameters, two of which are hidden that gets prefilled from the query below. The hidden parameters are DatatechClient and DatatechProduct.

SELECT ClientNameProduct, DatatechClient, DatatechProduct
FROM V_TranslationTable
WHERE (CMRNum = @.Cmr) AND (AcctNum = @.ClientNum)

Three parameters are shown and the report works fine as long as the CMRNum and AcctNum is found in the V_TranslationTable however, the error generates when they are not found. I looked through the other threads in this forum that deals with "Parameter is missing a value" but it didn't appear to be a solution shown.

Thanks in advance for any assistance you give.

I take it that when either CMRNum or AcctNum don't exist, you don't want the report to error out. Use this in your query - the report should run and return nothing, but won't error out:

Where IsNull(CMRNum,'') = @.Cmr AND IsNull(AcctNum,'') = @.ClientNum

|||

Hi Jamvir,

Thanks for responding.

I need to give more clearity. My report consists of six datasets and I'm working with RS2005. @.Cmr and @.ClientNum gets populated by a query in my first two datasets. My two hidden parameters @.DatatechClient and @.DatatechProduct are a part of my third dataset and they are the columns that are coming back blank from the query below.

SELECT ClientNameProduct, DatatechClient, DatatechProduct
FROM V_TranslationTable
WHERE (CMRNum = @.Cmr) AND (AcctNum = @.ClientNum)

Blank parameters for @.DatatechClient and or @.DatatechProduct are being passed to my fifth dataset query below.

SELECT EntityID, PrimaryDirInd, IsOnContract, [5DigitCode], EntityName, Addr1, City, State, Zip, Attn, Phone, ClientID, ProductID
FROM V_EntityDirectories
WHERE ((DirID = @.DirNum) AND (ClientID = @.DatatechClient) AND (ProductID = @.DatatechProduct))
ORDER BY EntityID

Also blank parameters for @.DatatechClient and or @.DatatechProduct are being passed to my sixth dataset query below.

SELECT PkgHdngs.ClientID, PkgHdngs.ProductID, PkgHdngs.DirID, PkgHdngs.HdngSeqNum, PkgHdngs.HdngText, PkgHdngs.HdngCode, PkgHdngs.DirVer, HdngAds.AdUnitID, HdngAds.AdRate
FROM PkgHdngs INNER JOIN
HdngAds ON PkgHdngs.ClientID = HdngAds.ClientID AND PkgHdngs.ProductID = HdngAds.ProductID AND PkgHdngs.DirID = HdngAds.DirID AND PkgHdngs.DirVer = HdngAds.DirVer AND PkgHdngs.HdngSeqNum = HdngAds.HdngSeqNum
WHERE ((PkgHdngs.DirID = @.DirID) AND (PkgHdgns.ClientID = @.DatatechClient) AND (PkgHdgns.ProductID = @.DatatechProduct))
ORDER BY PkgHdngs.ClientID, PkgHdngs.ProductID, PkgHdngs.DirID

Best regards

|||

Have Non queried default values for DatatechClient and DatatechProduct parameters and use the follwing expression for it:

=IIf(Count(Fields!DatatechClient.Value, "DataSet3") > 0, First(Fields!DatatechClient.Value, "DataSet3"), "SomeDefaultValueOrBlank")

and similarly for DatatechProduct