DSum HELP

[Kevin Clauberg]

I've tried to execute a query with a DSum field to give a
running total. Here is my DSum formula:

Run: DSum("Standard","Query Running Totals","DatePart('h',
[Hour])<=

---

")

....where Standard is a field in my query, Query Running
Totals is the name of the query, and the expression
indicates that I want the sum to include all records where
the Hr field on my query is less than or equal to the hour
of the field Hour in the query supplying the data.

As I've read on the knowledge base, this seems to mimic
the example, but I keep getting error messages indicating
incorrect operator, among others. If I take out the
expression, the DSum works fine, but includes all records
in the query. I just want this field to include the total
for the current record and all records before it.

Any help? Thanks.

 

[John Vinson]

I've tried to execute a query with a DSum field to give a
running total. Here is my DSum formula:

Run: DSum("Standard","Query Running Totals","DatePart('h',
[Hour])<=

---

")

---

You need to pull the

---

field out of the text string: you want Dsum
to see a number, not the name of a field:

Run: DSum("Standard","Query Running Totals","DatePart('h',
[Hour])<=" &

---

)

I'm assuming that

---

is an Integer or Long Int field in your query.

 

[Kevin Clauberg]

Thanks John. I do have a new problem, however...

I tried the suggestion and now I get an error message
indicating... The expression you entered as a query
parameter produced this error: 'The oject doesn't contain
the automation object 'Hour."

The field [Hour] is in the Query feeding this query, so
I'm confused. Could you help again? Thanks.

-----Original Message-----

I've tried to execute a query with a DSum field to give a
running total. Here is my DSum formula:

Run: DSum("Standard","Query Running Totals","DatePart ('h',
[Hour])<=

---

")

---

You need to pull the

---

field out of the text string: you want Dsum
to see a number, not the name of a field:

Run: DSum("Standard","Query Running Totals","DatePart ('h',
[Hour])<=" &

---

)

I'm assuming that

---

is an Integer or Long Int field in your query.



.

---

---

---

---

 

[John Vinson]

Thanks John. I do have a new problem, however...

I tried the suggestion and now I get an error message
indicating... The expression you entered as a query
parameter produced this error: 'The oject doesn't contain
the automation object 'Hour."

The field [Hour] is in the Query feeding this query, so
I'm confused. Could you help again? Thanks.

Does the Query named [Query Running Totals] include the field [Hour]?
And what is its datatype?

 

[Kevin Clauberg]

You are a patient person, John. Thank you. I may have
been doing too much renaming, so I decided to just make a
simple query with a DSum. Now I'm closer, but still
getting error messages.

Query Name: Query Running Totals (Another query in the DB)

Field: Per: Period
Type: Long Integer
Table: Quincy Pk (This is actually a query)
Total: Group By
Show: Yes

Field: Alt STD
Type: General Number (Created in the Quincy Pk query)
Table: Quincy Pk (This is actually a query)
Total: Sum
Show: Yes

Field: Run Tot: DSum("Alt STD","Quincy Pk","[Period]_
<="& [Per])
Total: Expression
Show: Yes


When I run this query, I get #Error in all the Run Tot
fields then an popup message tells me, "Syntax error
(missing operator) in query expression 'Sum(Alt STD)'.

Thanks again for all your help.

Kevin clauberg

-----Original Message-----

Thanks John. I do have a new problem, however...

I tried the suggestion and now I get an error message
indicating... The expression you entered as a query
parameter produced this error: 'The oject doesn't contain
the automation object 'Hour."

The field [Hour] is in the Query feeding this query, so
I'm confused. Could you help again? Thanks.

Does the Query named [Query Running Totals] include the field [Hour]?
And what is its datatype?


.

 

[John Vinson]

Query Name: Query Running Totals (Another query in the DB)

I'm not following this. I know you're trying to post all the needed
information, but (odd as it may sound) the grid representation is very
hard to follow; the apparently cryptic SQL text is MUCH easier. Please
open [Query Running Totals] in SQL view and copy and paste the SQL to
a message here. I *suspect* that the blanks in your fieldnames require
square brackets, but since I'm not certain what's actually going on
it's hard to say!

 

[Guest]

Your problem is that you are using the label [Per] in your
DSUM statement. Try using [Quincy Park].[Period] instead.

Cheers,
Sam

-----Original Message-----
You are a patient person, John. Thank you. I may have
been doing too much renaming, so I decided to just make a
simple query with a DSum. Now I'm closer, but still
getting error messages.

Query Name: Query Running Totals (Another query in the DB)

Field: Per: Period
Type: Long Integer
Table: Quincy Pk (This is actually a query)
Total: Group By
Show: Yes

Field: Alt STD
Type: General Number (Created in the Quincy Pk query)
Table: Quincy Pk (This is actually a query)
Total: Sum
Show: Yes

Field: Run Tot: DSum("Alt STD","Quincy Pk","[Period]_
<="& [Per])
Total: Expression
Show: Yes


When I run this query, I get #Error in all the Run Tot
fields then an popup message tells me, "Syntax error
(missing operator) in query expression 'Sum(Alt STD)'.

Thanks again for all your help.

Kevin clauberg

-----Original Message-----

Thanks John. I do have a new problem, however...

I tried the suggestion and now I get an error message
indicating... The expression you entered as a query
parameter produced this error: 'The oject doesn't contain
the automation object 'Hour."

The field [Hour] is in the Query feeding this query, so
I'm confused. Could you help again? Thanks.

Does the Query named [Query Running Totals] include the field [Hour]?
And what is its datatype?


.

.

 

[Kevin Clauberg]

Here is the SQL statement...

SELECT [Quincy Pk].Period AS Per, Sum([Quincy Pk].[Alt
STD]) AS [SumOfAlt STD], DSum("Alt STD","Quincy
Pk","[Period]<=" & [Per]) AS RunTot
FROM [Quincy Pk]
GROUP BY [Quincy Pk].Period;

Thanks again.

-----Original Message-----

Query Name: Query Running Totals (Another query in the

DB)

I'm not following this. I know you're trying to post all the needed
information, but (odd as it may sound) the grid representation is very
hard to follow; the apparently cryptic SQL text is MUCH easier. Please
open [Query Running Totals] in SQL view and copy and paste the SQL to
a message here. I *suspect* that the blanks in your fieldnames require
square brackets, but since I'm not certain what's actually going on
it's hard to say!


.

 

[Kevin Clauberg]

Thanks, Sam. I'll give this a try. I used an alternate
label because that's what the example in the knowledge
base did. Actually I tried to duplicate the example with
the Northwind database and it worked fine. I used the
exact same concept with my database and I get the errors.
Anyway, I'll try your suggestion. Thanks very much.

Kevin C.

-----Original Message-----
Your problem is that you are using the label [Per] in your
DSUM statement. Try using [Quincy Park].[Period] instead.

Cheers,
Sam
-----Original Message-----
You are a patient person, John. Thank you. I may have
been doing too much renaming, so I decided to just make a
simple query with a DSum. Now I'm closer, but still
getting error messages.

Query Name: Query Running Totals (Another query in the DB)

Field: Per: Period
Type: Long Integer
Table: Quincy Pk (This is actually a query)
Total: Group By
Show: Yes

Field: Alt STD
Type: General Number (Created in the Quincy Pk query)
Table: Quincy Pk (This is actually a query)
Total: Sum
Show: Yes

Field: Run Tot: DSum("Alt STD","Quincy Pk","[Period]_
<="& [Per])
Total: Expression
Show: Yes


When I run this query, I get #Error in all the Run Tot
fields then an popup message tells me, "Syntax error
(missing operator) in query expression 'Sum(Alt STD)'.

Thanks again for all your help.

Kevin clauberg

-----Original Message-----
On Thu, 16 Oct 2003 13:33:31 -0700, "Kevin Clauberg"

Thanks John. I do have a new problem, however...

I tried the suggestion and now I get an error message
indicating... The expression you entered as a query
parameter produced this error: 'The oject doesn't contain
the automation object 'Hour."

The field [Hour] is in the Query feeding this query, so
I'm confused. Could you help again? Thanks.

Does the Query named [Query Running Totals] include the field [Hour]?
And what is its datatype?


.

.

.

 

[Kevin Clauberg]

Well...I tried your suggestion, but I get the same error
message.

"Syntax error (missing operator) in query expression 'Sum
(Alt STD)'.

Plus I get #Error in all the fields.

Thanks for the idea. Any others are greatly appreciated.
I put my SQL statement in a reply to John Vinson.

-----Original Message-----
Your problem is that you are using the label [Per] in your
DSUM statement. Try using [Quincy Park].[Period] instead.

Cheers,
Sam
-----Original Message-----
You are a patient person, John. Thank you. I may have
been doing too much renaming, so I decided to just make a
simple query with a DSum. Now I'm closer, but still
getting error messages.

Query Name: Query Running Totals (Another query in the DB)

Field: Per: Period
Type: Long Integer
Table: Quincy Pk (This is actually a query)
Total: Group By
Show: Yes

Field: Alt STD
Type: General Number (Created in the Quincy Pk query)
Table: Quincy Pk (This is actually a query)
Total: Sum
Show: Yes

Field: Run Tot: DSum("Alt STD","Quincy Pk","[Period]_
<="& [Per])
Total: Expression
Show: Yes


When I run this query, I get #Error in all the Run Tot
fields then an popup message tells me, "Syntax error
(missing operator) in query expression 'Sum(Alt STD)'.

Thanks again for all your help.

Kevin clauberg

-----Original Message-----
On Thu, 16 Oct 2003 13:33:31 -0700, "Kevin Clauberg"

Thanks John. I do have a new problem, however...

I tried the suggestion and now I get an error message
indicating... The expression you entered as a query
parameter produced this error: 'The oject doesn't contain
the automation object 'Hour."

The field [Hour] is in the Query feeding this query, so
I'm confused. Could you help again? Thanks.

Does the Query named [Query Running Totals] include the field [Hour]?
And what is its datatype?


.

.

.

 

[John Vinson]

Here is the SQL statement...

SELECT [Quincy Pk].Period AS Per, Sum([Quincy Pk].[Alt
STD]) AS [SumOfAlt STD], DSum("Alt STD","Quincy
Pk","[Period]<=" & [Per]) AS RunTot
FROM [Quincy Pk]
GROUP BY [Quincy Pk].Period;

I'd suggest changing the GROUP BY to

GROUP BY Per

since you've aliased the Period field; and put square brackets around
the field and table names in the DSum:

DSum("[Alt STD]","[Quincy Pk]","[Period]<=" & [Per])

I'm assuming that Period is a numeric field, otherwise you'll need
quotemarks for Text or # delimiters for dates: e.g. if Period is a
Date/Time field,

DSum("[Alt STD]","[Quincy Pk]","[Period]<=#" & [Per] & "#")