Parameter Query with DSum

  • Thread starter Thread starter e133
  • Start date Start date
E

e133

I have a query which calculates a groups % of the total. I am using a Dsum to
calculate the % but when I introduce a date parameter i get the error message
"The expression you entered as a query parameter produced this error: Access
cant find the name 'Enter start date' you entered in the expression". The %
column is null in the query results. Any help with correct dsum format would
be appreciated.

Without the parameter the dsum returns the correct value. Below is the sql

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Create].[Name], [Cost]/DSum("Cost","Summary") AS [% of Total]
FROM [Create]
WHERE ((([Create].[T])="C") AND (([Create].[Due Date]) Between [Enter Start
Date] And [Enter End Date]))
GROUP BY [Create].[Name];
 
Thanks Ken.. Worked perfectly.. I guess I need to learn subs.. Thanks for the
input on the name..

KenSheridan via AccessMonster.com said:
I assume the DSum function is self-referencing the query here. Instead try
using a subquery on the original table to return the sum of the Cost column
and also restrict the subquery on the parameters:

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Name], [Cost]/
(SELECT SUM([Cost])
FROM [Create]
WHERE [T] = "C"
AND [Due Date] BETWEEN [Enter Start Date] And [Enter End Date])
AS [% of Total]
FROM [Create]
WHERE WHERE [T] = "C"
AND [Due Date] BETWEEN [Enter Start Date] And [Enter End Date]
GROUP BY [Name];

BTW I'd recommend avoiding Name as a column name as it’s the name of a built
in property in Access. More specific terms like ProductName, CustomerName,
EmployeeName etc are better.

Ken Sheridan
Stafford, England
I have a query which calculates a groups % of the total. I am using a Dsum to
calculate the % but when I introduce a date parameter i get the error message
"The expression you entered as a query parameter produced this error: Access
cant find the name 'Enter start date' you entered in the expression". The %
column is null in the query results. Any help with correct dsum format would
be appreciated.

Without the parameter the dsum returns the correct value. Below is the sql

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Create].[Name], [Cost]/DSum("Cost","Summary") AS [% of Total]
FROM [Create]
WHERE ((([Create].[T])="C") AND (([Create].[Due Date]) Between [Enter Start
Date] And [Enter End Date]))
GROUP BY [Create].[Name];
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top