DSUM error# Syntax problem?

[Phil Smith]

I am trying to use DSUM to create a running total via Method 1 in MS TID
290136.

I am getting an error in my DSUM statement. Very simple query:

SELECT SalesForGraph.Week AS CWeek, SalesForGraph.[2006Profit],
DSum("2006Profit","SalesForGraph","[WEEK] <= " & [Cweek]) AS RunTot
FROM SalesForGraph
ORDER BY SalesForGraph.Week;

The string for Criteria resolves to:
[week] <=1
[week] <=2

etc. What am I doing wrong?

Phil

 

[Ofer Cohen]

Mybe,

If the WEEk field type is text, then try

SELECT SalesForGraph.Week AS CWeek, SalesForGraph.[2006Profit],
DSum("2006Profit","SalesForGraph","Val([WEEK]) <= " & Val([Cweek])) AS RunTot
FROM SalesForGraph
ORDER BY SalesForGraph.Week;

 

[Phil Smith]

Changed it to

SELECT SalesForGraph.Week AS CWeek, SalesForGraph.[2006Profit],
DSum("2006Profit","SalesForGraph","Val([WEEK]) <= " & Val([Cweek]) & "
") AS RunTot
FROM SalesForGraph
ORDER BY SalesForGraph.Week;


Still errors out.

Phil

 

[Phil Smith]

More weirdness:

If I click on one of the error# cells in datasheet view, I get a dialog
box that says:
Syntax error (missing operator) in query expression Sum(2006Profit)

This error does not show up when the query is run.

 

[Duane Hookom]

Don't use CWeek in the DSum(). Try:

SELECT SalesForGraph.Week AS CWeek, SalesForGraph.[2006Profit],
DSum("2006Profit","SalesForGraph","[WEEK] <= " & [week]) AS RunTot
FROM SalesForGraph
ORDER BY SalesForGraph.Week;

--
Duane Hookom
Microsoft Access MVP

More weirdness:

If I click on one of the error# cells in datasheet view, I get a dialog
box that says:
Syntax error (missing operator) in query expression Sum(2006Profit)

This error does not show up when the query is run.

Changed it to

SELECT SalesForGraph.Week AS CWeek, SalesForGraph.[2006Profit],
DSum("2006Profit","SalesForGraph","Val([WEEK]) <= " & Val([Cweek]) & "
") AS RunTot
FROM SalesForGraph
ORDER BY SalesForGraph.Week;


Still errors out.

Phil