Run Report asks for Parameters it already has?

[mcupito]

I'm creating a report that creates a value from AwardedUnits * NetAssetValue, and creates a check using that value (it does a couple other irrelevant things, also.)

I take the Sum() of the awards, and here is the SQL:

SELECT BenePayoutTbl1.BeneID, 
BenePayoutTbl1.BeneFirst, 
BenePayoutTbl1.BeneLast,
 BenePayoutTbl1.PrimaryBene,
 Sum(BenePayoutTbl1.UnitsRemaining) AS SumOfUnitsRemaining,
 AstBeneficiaryTbl.BenStreet,
 AstBeneficiaryTbl.BenCity, 
AstBeneficiaryTbl.BenState, 
AstBeneficiaryTbl.BenZip,
 [FederalTax] AS Expr1,
 [StateTax] AS Expr2, 
BenePayoutTbl1.NAV_Dt,
 NAV_Tbl.NetAssetValue
FROM NAV_Tbl
 INNER JOIN (AstBeneficiaryTbl
 INNER JOIN BenePayoutTbl1 
ON AstBeneficiaryTbl.BeneficiaryID = BenePayoutTbl1.BeneID)
 ON NAV_Tbl.NAV_Date = BenePayoutTbl1.NAV_Dt
GROUP BY BenePayoutTbl1.BeneID,
 BenePayoutTbl1.BeneFirst, 
BenePayoutTbl1.BeneLast, 
BenePayoutTbl1.PrimaryBene, 
AstBeneficiaryTbl.BenStreet,
 AstBeneficiaryTbl.BenCity, 
AstBeneficiaryTbl.BenState,
 AstBeneficiaryTbl.BenZip, 
[FederalTax], [StateTax], 
BenePayoutTbl1.NAV_Dt,
 NAV_Tbl.NetAssetValue;
 

The query gives me 107 = [SumOfRemainingUnits], however, when I run the report it asks me for [SumOfRemainingUnits].

Anyone have any ideas?

[jimatqsi]

Look at the query behind the report. Is the name of the column [SumOfRemainingUnits]? Either the query, or some object in the report is looking for that field and it does not exist with that name. Perhaps it is renamed in the report recordset.

Jim

[mcupito]

Yes, it's just a Sum of a field. I checked the spelling and everything seems to be fine.

It was actually [SumOfUnitsRemaining].

Here is the SQL:

SELECT 
    BenePayoutTbl1.BeneID, 
    BenePayoutTbl1.BeneFirst,
    BenePayoutTbl1.BeneLast, 
    BenePayoutTbl1.PrimaryBene, 
    Sum(BenePayoutTbl1.UnitsRemaining) AS SumOfUnitsRemaining,
    AstBeneficiaryTbl.BenStreet, 
    AstBeneficiaryTbl.BenCity, 
    AstBeneficiaryTbl.BenState, 
    AstBeneficiaryTbl.BenZip, 
    [FederalTax] AS Expr1, 
    [StateTax] AS Expr2, 
    BenePayoutTbl1.NAV_Dt, NAV_Tbl.NetAssetValue
 FROM NAV_Tbl
 INNER JOIN (AstBeneficiaryTbl 
INNER JOIN BenePayoutTbl1 
    ON AstBeneficiaryTbl.BeneficiaryID = BenePayoutTbl1.BeneID) 
       ON NAV_Tbl.NAV_Date = BenePayoutTbl1.NAV_Dt
GROUP BY BenePayoutTbl1.BeneID, BenePayoutTbl1.BeneFirst, 
BenePayoutTbl1.BeneLast, BenePayoutTbl1.PrimaryBene, 
AstBeneficiaryTbl.BenStreet, AstBeneficiaryTbl.BenCity, 
AstBeneficiaryTbl.BenState, AstBeneficiaryTbl.BenZip,
 [FederalTax], [StateTax], BenePayoutTbl1.NAV_Dt, NAV_Tbl.NetAssetValue;
 

[zmbd]

Yes, it's just a Sum of a field. I checked the spelling and everything seems to be fine

Are you still haveing the issue, or did you find the typo?

[mcupito]

Hey, zmbd, thanks for the reply.

I am only having the issue when the report using the underlying query (the SQL I posted) is being ran. If I ran the query from the Object list it runs fine and only asks me for [FederalTax] and [StateTax].

[zmbd]

Double check line 5 in op the "as" name
It does not match the name you subsequently gave for the prompt.

As jimatqsi has pointed out, something on the report-form is asking for this name and it does not match anything within the underlying recordset; thus, the prompt.

[mcupito]

Okay - I found the fields on the report. If I delete them, the subreports return no data, but if I press "OK" when the parameter box pops up, the data loads fine. Any idea on how to squelch it?

[zmbd]

Check the properties of the fields.... there's something not set correctly with them and it has to do with the names for the parameters you are being prompted for....

[mcupito]

The only thing I am seeing is ORDER BY NAV_Date DESC

[mcupito]

Doh, deleting the Order By worked... -_-

[zmbd]

It is the little things that will cause the most damage.
That'll be 100KG of Richart or Callebaut Chocolate
(^_^)

[mcupito]

Haha would you like that served with a warm glass of milk?

[zmbd]

hmmm... no, they both make Excellent truffles and other wonderful confectionary items... which I am fairly good at making )slobber...and...smakcckingoflipsoundsinthebackgro und(

(O_O)