Query results in report !?

[Lockstock2SB]

Hi all

Ive made a query that basically counts the number of records that have a check box checked - that works fine (Query produces on cell in one column with the total number inside) When i come to create a report to display the results of the query - I insert a text box with the following :

=[QryCountClosedCalls]![ClosedID]

QryCountClosedCalls being the name of the query, and ClosedId being the name of the column.

When i run the report i get a message box prompting to "Enter parameter value" for QryCountClosedCalls. As far as i know i am doing this correctly !!?

Can someone help please ! )

Thanks

Steve

 

[Steve101]

Steve,

Try the following; it will hopefully put you on the right track:

(a) Create a new report
(b) In the report properties, ensure that RecordSource property is set to "QryCountClosedCalls"
(c) Add a new text box to the report.
(d) Change the name property of the text box to "ClosedId"
(e) Change the ControlSource property of the text box to "ClosedId"
(f) Save and run the report.

This should produce the result you want, using the query as the recordsource for the report, and its ClosedId as the source of the control on the report.

Hope this helps,
Cheers,
Steve

 

[Lockstock2SB]

Hi Steve

Thanks for the reply - i didnt get notified about the reply for some reason - so i only just got it.

I understand your solution - but unfortunately i need various things from my report.Here are some of them :

(All the data needed is located in 1 table)

1)Total number of records in the table
2)Total number still marked as open (check box)
3)Total number of records marked as closed (same check box)
4)Total number of records that have "A" in the name column
5)Total number of records that have "B" in the name column

I've based the report on the table itself - and have managed to get the total number of records =Count([ID]) But the rest all need to be derived from queries i guess !?

Is their an easier way around this ? /

Steve

 

[pjfox]

I would suggest the CountIF function.

Syntax is COUNTIF(range,criteria)


Pat
Stuck in a Cornfield in Iowa

 

[CosmoKramer]

Check out Access help for the DCount function. You could set up an unbound text box with its control source something like:

=DCount("[ID]", "TableName", "[chkOpenClosed] = -1"

would return the count of those check boxes that are checked.......

 

[Lockstock2SB]

Thats brilliant !

Just one more question :

From the results on my first Dcount text box - can i then have another that counts the results and filters it down even more ? For example :

(Total no. of records in table = 1250+)

Results of Dcount text box =

No of records checked = 250

(Then do another count to provide more detail on the selected records) :

170 of these records are Person A
80 of thee records are Person B

Thanks again !!
Steve

 

[CosmoKramer]

No, you will have to use a seperate DCount statement for each one with its own criteria. All that us being returned with a DCount is the count, not actual records...

 

[Lockstock2SB]

Thanks !!

 

[Lockstock2SB]

Great - i've got what i want - but i've now just discovered that the print preview has created a new page for every record in the table !?

How would i limit the report to just 1 page ?

Thank again for your help guys - you've been superb !

 

[CosmoKramer]

If all you want in your report is these total fields, move them to the report footer and reduce the size of the Detail section. Then they'll only appear once......