Dsum on a report

G

Guest

I am trying to may a running total of the 11 months to put on a monthly
report. The monthly report has a "BETWEEN...AND" parameter. I tried using

=DSum([NumberofExceedances], "Opacity", BETWEEN DateSerial(Year(Date()),
Month(Date()), 0) AND DateSerial(Year(Date()), Month(Date())-11, 1)))

in the control field

This did not work at all.

NumberofExceedances is the field that I am trying to sum.

Opacity is the table the field is located in.

BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1))) is the criteria that I need
to total by.
 
D

Douglas J. Steele

What does "This did not work at all" mean?

Was there an error message? If so, what was it?

If there wasn't an error message, did you get an answer, but it was wrong,
or did you get something else?
 
F

fredg

I am trying to may a running total of the 11 months to put on a monthly
report. The monthly report has a "BETWEEN...AND" parameter. I tried using

=DSum([NumberofExceedances], "Opacity", BETWEEN DateSerial(Year(Date()),
Month(Date()), 0) AND DateSerial(Year(Date()), Month(Date())-11, 1)))

in the control field

This did not work at all.

NumberofExceedances is the field that I am trying to sum.

Opacity is the table the field is located in.

BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1))) is the criteria that I need
to total by.
Click to expand...

You left out the name of the date field that is supposed to be Between
... And ... etc.

=DSum([NumberofExceedances], "Opacity", "[DateFieldName] BETWEEN
DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1))

I believe you also had 1 closing parenthesis too many.
 
G

Guest

Not work at all meant, I got a syntax error. When I took the criteria out of
the function, I got "error#" on the report.

Douglas J. Steele said:
What does "This did not work at all" mean?

Was there an error message? If so, what was it?

If there wasn't an error message, did you get an answer, but it was wrong,
or did you get something else?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


nevpoe said:
I am trying to may a running total of the 11 months to put on a monthly
report. The monthly report has a "BETWEEN...AND" parameter. I tried
using

=DSum([NumberofExceedances], "Opacity", BETWEEN DateSerial(Year(Date()),
Month(Date()), 0) AND DateSerial(Year(Date()), Month(Date())-11, 1)))

in the control field

This did not work at all.

NumberofExceedances is the field that I am trying to sum.

Opacity is the table the field is located in.

BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1))) is the criteria that I
need
to total by.
Click to expand...
Click to expand...
 
G

George Nicholson

1) DSum expects all 3 of its arguments to be in the form of strings.
2) what's the name of the Datetime field in Opacity you are BETWEENing? You
need to supply it.

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1)")

HTH,
 
G

Guest

Thanks that worked. But it brought up a new question. I want the totals by
Unit and it gave me all the Units. How can I do that?

George Nicholson said:
1) DSum expects all 3 of its arguments to be in the form of strings.
2) what's the name of the Datetime field in Opacity you are BETWEENing? You
need to supply it.

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1)")

HTH,


nevpoe said:
I am trying to may a running total of the 11 months to put on a monthly
report. The monthly report has a "BETWEEN...AND" parameter. I tried
using

=DSum([NumberofExceedances], "Opacity", BETWEEN DateSerial(Year(Date()),
Month(Date()), 0) AND DateSerial(Year(Date()), Month(Date())-11, 1)))

in the control field

This did not work at all.

NumberofExceedances is the field that I am trying to sum.

Opacity is the table the field is located in.

BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1))) is the criteria that I
need
to total by.
Click to expand...
Click to expand...
 
G

George Nicholson

Well, that depends on how you're doing this.

the following would give you the sum of Exceedances for records where the
Unit field had a value of Boston for the specified date range:

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1) AND [Unit] = 'Boston'")

If your report is grouped by Unit & you want dynamic results, then this
should work within that grouping:

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1) AND [Unit] = '" & [Unit] &
"''")

HTH,




nevpoe said:
Thanks that worked. But it brought up a new question. I want the totals
by
Unit and it gave me all the Units. How can I do that?

George Nicholson said:
1) DSum expects all 3 of its arguments to be in the form of strings.
2) what's the name of the Datetime field in Opacity you are BETWEENing?
You
need to supply it.

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1)")

HTH,


nevpoe said:
I am trying to may a running total of the 11 months to put on a monthly
report. The monthly report has a "BETWEEN...AND" parameter. I tried
using

=DSum([NumberofExceedances], "Opacity", BETWEEN
DateSerial(Year(Date()),
Month(Date()), 0) AND DateSerial(Year(Date()), Month(Date())-11, 1)))

in the control field

This did not work at all.

NumberofExceedances is the field that I am trying to sum.

Opacity is the table the field is located in.

BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1))) is the criteria that I
need
to total by.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I tried

If your report is grouped by Unit & you want dynamic results, then this
should work within that grouping:

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1) AND [Unit] = '" & [Unit] &
"''")
Click to expand...

But I keep getting error# on the report.

George Nicholson said:
Well, that depends on how you're doing this.

the following would give you the sum of Exceedances for records where the
Unit field had a value of Boston for the specified date range:

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1) AND [Unit] = 'Boston'")

If your report is grouped by Unit & you want dynamic results, then this
should work within that grouping:

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1) AND [Unit] = '" & [Unit] &
"''")

HTH,




nevpoe said:
Thanks that worked. But it brought up a new question. I want the totals
by
Unit and it gave me all the Units. How can I do that?

George Nicholson said:
1) DSum expects all 3 of its arguments to be in the form of strings.
2) what's the name of the Datetime field in Opacity you are BETWEENing?
You
need to supply it.

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1)")

HTH,


I am trying to may a running total of the 11 months to put on a monthly
report. The monthly report has a "BETWEEN...AND" parameter. I tried
using

=DSum([NumberofExceedances], "Opacity", BETWEEN
DateSerial(Year(Date()),
Month(Date()), 0) AND DateSerial(Year(Date()), Month(Date())-11, 1)))

in the control field

This did not work at all.

NumberofExceedances is the field that I am trying to sum.

Opacity is the table the field is located in.

BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1))) is the criteria that I
need
to total by.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Got it.

Should be:
=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1) AND "[Unit] = " & [Unit])
Click to expand...
Click to expand...

nevpoe said:
I tried

If your report is grouped by Unit & you want dynamic results, then this
should work within that grouping:

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1) AND [Unit] = '" & [Unit] &
"''")
Click to expand...

But I keep getting error# on the report.

George Nicholson said:
Well, that depends on how you're doing this.

the following would give you the sum of Exceedances for records where the
Unit field had a value of Boston for the specified date range:

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1) AND [Unit] = 'Boston'")

If your report is grouped by Unit & you want dynamic results, then this
should work within that grouping:

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1) AND [Unit] = '" & [Unit] &
"''")

HTH,




nevpoe said:
Thanks that worked. But it brought up a new question. I want the totals
by
Unit and it gave me all the Units. How can I do that?

:

1) DSum expects all 3 of its arguments to be in the form of strings.
2) what's the name of the Datetime field in Opacity you are BETWEENing?
You
need to supply it.

=DSum("[NumberofExceedances]", "Opacity", "[NameofDateFieldInOpacity]
BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1)")

HTH,


I am trying to may a running total of the 11 months to put on a monthly
report. The monthly report has a "BETWEEN...AND" parameter. I tried
using

=DSum([NumberofExceedances], "Opacity", BETWEEN
DateSerial(Year(Date()),
Month(Date()), 0) AND DateSerial(Year(Date()), Month(Date())-11, 1)))

in the control field

This did not work at all.

NumberofExceedances is the field that I am trying to sum.

Opacity is the table the field is located in.

BETWEEN DateSerial(Year(Date()), Month(Date()), 0) AND
DateSerial(Year(Date()), Month(Date())-11, 1))) is the criteria that I
need
to total by.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Selecting A single Month From a Table with Multiple Months 5
Query to Complex with Criteria 1
Date Serial Problem 2
grouping for report 6
John W. Vinson...continued help please 4
Error on Query with DateSerial() 0
Syntax for previous year with DateSerial 3
Help with a Macro! 4

Top