Hi Richard,

My report is grouped by YEARMONTH and then by JOBTYPE.
YEARMONTH is numeric and JOBTYPE is text. Details are hidden as they number in the hundreds.

I have a query that is to run after the user chooses a date from a combo box.  The combo box is called SelectYearMonth and is populated by YEARMONTH in HBELP_FINAL_Q.  This part works great.

Once the YEARMONTH is selected, I need to calculate the total Pages for each JOBTYPE for the chosen YEARMONTH.  There are four JOBTYPES and the types appear on the report correctly.

Here is where I run into problems, how to calculate the page totals for the YEARMONTH selected by the user by JOBTYPE. The total is to be displayed in a text box called TotalPagesTextbox that I have in the JOBTYPE footer. The AfterUpdate() code I am trying to use is:

Me.TotalPagesTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & "' AND JOBTYPE = '" & Me.[JOBTYPE] & "'")

I know there is something I am missing in the syntax here.

Can you please help?

Thanks,
Diana
 

---

Answer from Richard Rost:

If YearMonth is an unbound combobox on your form, it might still be returning a TEXT value which could throw off your Dsum. Try using

CLng(SelectYearMonth)

in your Dsum statement to convert that value to a number (long) beforehand… unless the data is stored as TEXT in your table, in which case don't convert it and put quotes around it instead (like you did with JobType).

Also, what is exactly is "Pages"? Is that also a field in your query? It would have to be to use the Dsum function to add them up. If you're trying to calculate the actual number of PRINTED pages in your report, that's a whole different monster - and you won't be able to use Dsum to do that.