Access 2000 Report Summing

[ccoop61]

I have created a report that runs off a crosstab query which allows users to select their criteria. I am using DSum to create subtotals from the report, but Access is summing totals from the universe of the crosstab query and not just the criteria that was selected. Is there a way that I can get Access to calculate the DSum from what the user selected to run the query?

 

[Anne Troy]

Make sure the record source for the control in which you use the DSum is set to the query and not the table.

 

[ccoop61]

The record source referenced in the DSum is set to the query. When the user selects the report, they then enter in their criteria that should be shown on the report. All of the information in the report reflects what was selected by the user except for the DSums I have added.

 

[Anne Troy]

Sorry. That's all I've got without seeing it.

 

[ccoop61]

Thanks for trying. Do you know if Access could be closing the query as the user ran it before it calculates the DSum? The reason I'm wondering is because the sum I am getting is what the query returns when no criteria is selcted.

 

[Anne Troy]

I'm not sure. How is the criteria being asked? Through a regular query criteria? Or query by form?

 

[ccoop61]

It's query by form. A form is set up where the user selects the report they want to run and then select the criteria...it's all done on one form.

 

[Anne Troy]

Def'ly something the matter with the form then. Sorry--I'm crap at troubleshooting Access without seeing the DB.

 

[downwitchyobadself]

Try building a non-crosstab query which draws data from the crosstab results. Add the crosstab to the QBE window's upper half, and you can select its "fields" as you would with any other query. Then set your criteria there, to match the form fields. And make any DSum statements match that query. Though, in theory, if the criteria are already in the form, there's no reason to use DSum.