Using Dsum in TextBox

[crimedog]

I am stumped - that I was on the right track.


I have a Query: MgrAnnualReportQ
I have a Report: AnnualResultsMgr

The Query takes a filter from a ComboBox on a form - (choose the Mgr then press button to run report)
If I run the Query and put in the Mgr (enter Parameter value) - the query runs fine.
Then I run the report (entering the parameter value)
with this Dsum formula in the Expression Builder for the Control Source for a Text Box:

=DSum("[DealRevenue]","[MgrAnnualReportQ]","[InvoiceMonth]=1 And [InvoiceYear]=Year(Date())")

Access shows "calculating" and then returns #error in the report.

Any ideas?

[June7]

Is InvoiceYear a text or number type field?

[crimedog]

it is a calculated field that returns an integer - I set the field type as number - re ran the report and got #ERROR

[June7]

Calculated field in table? Don't know why it fails for you. I just tested and it works for me. However, not using a query as the source, just table.

If you want to provide db for analysis, follow instructions at bottom of my post.

[crimedog]

Here is a test file - I am sure that it is a simple fix?

[June7]

In the posted db, the DSum references field Revenue but neither Deals table nor SalesQ have that field. They have SalesRevenue.

Don't need the Rep field in the query twice.

[crimedog]

Okay - changed the field to [SalesRevenue]
And deleted the unfiltered Rep field from query

Still returning error.

[June7]

I made those same two edits in your posted db and it works.

Also, the RowSource for the combobox does not need the GROUP BY clause.

[crimedog]

Racking my brain - still not working. If you see what I am doing wrong - Please let me know...

[June7]

Here's what I did.

1. downloaded and opened db, opened ChooseRep form

2. selected rep Andy in combobox

3. opened report - no error, value displayed is 175,000.00

Cannot replicate issue. No idea why it fails for you.

Still don't need the GROUP BY clause in the combobox RowSource, however, having it should not cause issue.

[crimedog]

wow every time I open it (and put Andy) I get #Error in the Text box. I wonder if there is something wrong with my Access installation. Thanks for all the help

[crimedog]

Update - I was not opening through the form, just trying to shortcut it - Open report, respond to the Message Boxes asking for the info from the form, then getting errors
As soon as I opened the form, filled in info and the used the button on the form to open the report - it worked.

Lesson learned, Thanks for all the help.