Problem with Running Total Query 2

[JOEYB99]

I am using Access 2007 and having trouble getting a running total in a query.

Here is the basic data, coming from a table called tblLabourHours2

ID (autonumber generated)
Formatted Cost Code
TransDate
Hours

My goal is to get a running total of hours for each cost code. A cost code may have hours for several different dates.

I need to know the running total for each date within a cost code since I may run reports/queries based on a date range.



The columns of my query has ID, Formatted Cost Code, TransDate, Hours, and the following expression in the next column

Expr1: DSum("Total Hours","tblLabourHours2","Formatted Cost Code = " & [Formatted Cost Code] & " And ID <=" & [ID])


I get #ERROR in the expression column.

What am I doing wrong?

 

[PHV]

What about this ?
DSum("Hours","tblLabourHours2","[Formatted Cost Code]='" & [Formatted Cost Code] & "' And ID<=" & [ID])

If [Formatted Cost Code] is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Thank you for the prompt reply PHV but it did not work, if I applied your single quotes properly.

Please confirm. You have a single quote just to the right of the first equal sign, and then you have the second single quote just to the left of the And. Correct?

The cost code is a text field.

 

[PHV]

Please, post the SQL code of your query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Okay then PHV, how's this?

SELECT tblLabourHours2.ID, tblLabourHours2.[Formatted Cost Code], tblLabourHours2.[TransDate], tblLabourHours2.[Total Hours], DSum("Total Hours","tblLabourHours2","Formatted Cost Code =" & [Formatted Cost Code] & "And ID <=" & [ID]) AS Expr1
FROM tblLabourHours2;

 

[PHV]

So, try this:

SELECT ID, [Formatted Cost Code], TransDate, [Total Hours]
, DSum("[Total Hours]","tblLabourHours2","[Formatted Cost Code]='" & [Formatted Cost Code] & "' AND ID<=" & [ID]) AS RunningTotalHours
FROM tblLabourHours2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Thank you PHV. It is still not working but I think we are close.

When I click on the #ERROR result I get the following message,

Syntax error (missing operator) in query expression 'Formatted Cost Code = 'LAB.123' AND ID <= 4'.

It appears to be something do with the proper use of single/double quotes.

The ID field is a numeric field and the cost code field is set up as text.

 

[Andrzejek]

Do:[tt]

Dim strSQL As String
strSQL = "Select ..."[blue]
Debug.Print strSQL[/blue]
[/tt]

and show us what you have in the Immediate Window.
This way (probably) you will see what's wrong with it....

Have fun.

---- Andy

 

[JOEYB99]

Okay Andy, thank you for the suggestion but I'm not familiar with that code. Is that VBA or SQL?

Where exactly do I input it?

 

[PHV]

Again,

SELECT ID, [Formatted Cost Code], TransDate, [Total Hours]
, DSum("[!][[/!]Total Hours[!]][/!]","tblLabourHours2","[!][[/!]Formatted Cost Code[!]][/!]='" & [Formatted Cost Code] & "' AND ID<=" & [ID]) AS RunningTotalHours
FROM tblLabourHours2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Thanks PHV, but can you please clarify.

I see the red square brackets. Should I remove them?

 

[JOEYB99]

Eureka, it now works! Thank you PHV. You are correct about the exact placement of the square brackets.

I also appreciate the input from Andy. I was not familiar with that debugging technique. I did not use it but plan to use it in the future.