DSUM Baffle

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report with two report footer. As you can see:
=Sum([Total Amt Paid])

=DSum([Total Amt Paid],"Main Database","Format(Date,""yyyymm"") = """ &
Format([End Date],"yyyymm") & """")

Now, this report runs off a query (QryDates) off the "Main Database" The
QryDates asks the user for two criteria [Start Date] and [Ending Date] Which
work fine.

The DSum is just looking at the Main Database and totaling the date "yyyy"
and "mm" So, the query is a partial date, Example 10/01/2005 to 10/20/2005
and the DSUM is all of Oct 2005. The problem I'm having is the DSUM sometimes
works. Now, I checked the data and the "Total Amt Paid" is format currency. I
ran a test on the database and enter a total of nine record with the value of
$9.00 in each record. The Sum totals $49.00 and the DSum total $49.00 which
worked BUT when I changed one of the record to $9.99 dollars the Sum was
$49.99 and the DSum was $49.00. Then I changed the one record to $1.00 and
the Sum was $41.00 and the DSum was $49.00.

Why is this happening? Thank you
 
Is Date is a name of a field in the table?
In that case then date is a reserved name in access, which return the
current date.
It will be OK as long that you put it in square brackets.

Try this
=DSum([Total Amt Paid],"[Main Database]","Format([Date],"yyyymm") = '" &
Format([End Date],"yyyymm") & "'")
 
The first argument must be a string and the first yyyymm can't be " so the
expression might be:
=DSum("[Total Amt Paid]", "[Main Database]", "Format([Date],'yyyymm') = '" &
Format([End Date],"yyyymm") & "'")

--
Duane Hookom
MS Access MVP


Ofer said:
Is Date is a name of a field in the table?
In that case then date is a reserved name in access, which return the
current date.
It will be OK as long that you put it in square brackets.

Try this
=DSum([Total Amt Paid],"[Main Database]","Format([Date],"yyyymm") = '" &
Format([End Date],"yyyymm") & "'")

--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck



bladelock said:
I have a report with two report footer. As you can see:
=Sum([Total Amt Paid])

=DSum([Total Amt Paid],"Main Database","Format(Date,""yyyymm"") = """ &
Format([End Date],"yyyymm") & """")

Now, this report runs off a query (QryDates) off the "Main Database" The
QryDates asks the user for two criteria [Start Date] and [Ending Date]
Which
work fine.

The DSum is just looking at the Main Database and totaling the date
"yyyy"
and "mm" So, the query is a partial date, Example 10/01/2005 to
10/20/2005
and the DSUM is all of Oct 2005. The problem I'm having is the DSUM
sometimes
works. Now, I checked the data and the "Total Amt Paid" is format
currency. I
ran a test on the database and enter a total of nine record with the
value of
$9.00 in each record. The Sum totals $49.00 and the DSum total $49.00
which
worked BUT when I changed one of the record to $9.99 dollars the Sum was
$49.99 and the DSum was $49.00. Then I changed the one record to $1.00
and
the Sum was $41.00 and the DSum was $49.00.

Why is this happening? Thank you
Click to expand...
Click to expand...
 
Back
Top