Help with DSUM Function (1 Viewer)

I have a query with the following fields:
Topic Duration
Topic_Order
Meeting_ID

Data source: qry_topic_combo
Topic_Duration Topic_Order Meeting_ID
15 1 7
45 2 7
15 3 7
30 4 7
15 1 8
30 2 8
30 3 8
15 4 8

I am trying to generate a running total of topic duration by topic_order and meeting ID for previous topics. So the record for meeting_id 1 with topic_order 1 would have a zero value, topic_order 2 would have a value of 15, etc. The output for the above data should look like this :

Topic_Duration Topic_Order Meeting_ID Running_Total
15 1 7 0
45 2 7 15
15 3 7 60
30 4 7 75
15 1 8 0
30 2 8 15
30 3 8 45
15 4 8 75

So the first expression I wrote got a running total by topic_order but ignored meeting_id:

expression:

Running_Total: DSum(" [Topic_Duration] ","QRY_TOPIC_COMBO","[Topic Order]<" & [Topic Order] & "")

Output:
Topic_Duration Topic_Order Meeting_ID Running_Total
15 1 7 0
45 2 7 30
15 3 7 105
30 4 7 150
15 1 8 0
30 2 8 30
30 3 8 105
15 4 8 150

So far so good. When I tried to add the second criteria, though, My running total just becomes a total for the entire column:

Expression:
Running_Total: DSum(" [Topic_Duration] ","QRY_TOPIC_COMBO","[Topic Order]<" & [Topic Order] & "" And [Meeting_id]=[meeting_id])

Output:
Topic_Duration Topic_Order Meeting_ID Running_Total
15 1 7 195
45 2 7 195
15 3 7 195
30 4 7 195
15 1 8 195
30 2 8 195
30 3 8 195
15 4 8 195

What am I doing wrong?

Compare how you handled [Topic Order] and with what you did with [meeting_id]. [Topic Order] seems to have more stuff around it.

Also, perhaps its just a typo (since you got results), but you have double quotes that should cause a syntax error after the second ampersand.

Plog,

Thanks -- I change the quotes to match your suggestion but I get the same results.

Huh? That was only 1 of the things I posted about and not the issue I addressed as being the culprit.

I'm not sure I understand. I swapped my criteria for yours and got the same result -- am I missing something?

I provided no criteria. I told you to look at how you built your criteria string. Why does [Topic Order] have so many more quote marks and other characters all around it?

The quotes were copied from other examples I've seen for dsum. I have tried different variations of quotes but that doesn't seem to be the issue.

I advise you to understand what you are trying to do, not just throw code together and hope. In that vain, here's the documentation for DSum:

https://www.techonthenet.com/access/functions/domain/dsum.php

The problem you are having is in building the last argument, the criteria string. You are trying to mix literals (as the characters appear) and variables (the values represented by other characters) . When you want to insert the value of a variable you must escape out of the literal, put the value you want in it, then go back to writing a literal.

This works:

"[Topic Order]<" & [Topic Order]

The part before the ampersand (&) is between quotes ans is the literal part--that is its going into your criteria as you see it.

The part after the ampersaond is a variable. Whatever value is in [Topic Order] gets placed into the string, not the actual characters "[Topic String]". Let's say the varialbe [Topic Order] equals 4. The above code is then interpreted by the computer as:

"[Topic Order] < 4"

Because it knows the second [Topic Order] is a variable and uses its value.

You need to do the same thing with the [Meeting_id] part. give it a shot and if it doesn't work, post back what you tried.

Running_Total: DSum(" [Topic_Duration] ","QRY_TOPIC_COMBO","[Topic Order]<" & [Topic Order] & "" And "[Meeting_id]=" & [meeting_id] & "")

returns 195 for each record -- the sum of the field

That double quote is back.

Also, everything from the AND to the equal sign is part of the same literal

Removing the double quotes gets a syntax error:

Running_Total: DSum(" [Topic_Duration] ","QRY_TOPIC_COMBO","[Topic Order]<" & [Topic Order] & And "[Meeting_id]=" & [meeting_id] & )

I'm not sure what the correct format is -- how would you format this to get it work?

You shouldn't have removed both quotes--just one. This shoudl work:

"[Topic Order]<" & [Topic Order] & " And [Meeting_id]=" & [meeting_id])

"literal" & variable & "literal" & variable & "literal" &...

That works!! Thanks for your help.