Totaling Fields in Report slightly different (A2K)

Topic

New Reply

I also have a report which I need to count the number of occurrences based on a floor or location number. I need to count the number of disciplinary hearings based on the floor the incident happened. and print out in different boxes the results.

For example, on the 2nd floor ([Floor]) there were 8 incidents, on the 4th floor there were 6 all in the 06/01/03 to 06/30/03 date range ([DateIncident]) field.

Ther report needs to total the number by floor and place the number in a separate box under the column heading for the correct location. Is there a way to apply a filter to a text box along with the code? I have written some code, based on the answer given by Hans, as =Abs(Count([Floor]). This when run asks for the floor, but if I put in “02” as the response it totals all the text boxes as if the filter was to “02” I need the report to total for each floor without any input if possible. I have already restricted the data to selecting the previous month but can’t figure out how to filter to the floor as well.

Please help.

[Floor] is a text field with two digits length. It is restricted to 02, 04, 06, 08, 10, 12, 13, 01, KI, BA for the floors in a jail that house inmates.

Jail Administrator Medical

Reply | Quote

Replies

What is the record source of your report?
– What type (table, stored query, SQL string)?
– If a query or SQL string, post the SQL.
And how do you restrict it to a certain period in time?

We will need to know this to be able to give a specific answer.

Reply | Quote

I am using a query (qryLine1) with the date selected based on the date of incident, Floor and an expression for floor which counts the number of entries by floor between dates. Three fields to select.

I am using one stored query, I don’t want to write a different query for each box when all I need to do is to change the filter for the different floor. I don’t know how to make an SQL string. I can write the query and view the SQL but how do I move that to the report? There will be 88 boxes on the report when done and I don’t want to make a query for each one. I just believe access has a way to change the filter option alone for the text boxes in the report but I don’t have any idea on how to do it. Maybe I am wrong.

SELECT Count(DateSerial(Year(Date()),Month(Date()-1),0)) AS Numbers
FROM tblViolation
WHERE (((tblViolation.[Date of Incident]) Between DateSerial(Year(Date()),Month(Date()-1),0) And DateSerial(Year(Date()),Month(Date())-1,0)))
GROUP BY tblViolation.Floor
HAVING (((tblViolation.Floor)=”13″))
ORDER BY tblViolation.Floor DESC;

I can copy and paste this into the data control source but it only lists the first line and gives me an error: Extra ) in query expression…

I have counted, carefully, the brackets and there are the same number of opening and closing, plus I copied the expression straight from the SQL view and pasted it in.

Reply | Quote

Hi JailAdmin,

You may be able to use a crosstab query, but the situation still isn’t clear to me. In the first post in this thread, you mention 10 floors (02, 04, 06, 08, 10, 12, 13, 01, KI, BA). How does this lead to 88 boxes?

BTW, you can’t just enter a SQL string into the control source of a text box.

Reply | Quote

Maybe you could do this via a crosstab query, with rows as dates and columns as the floors.
When creating such a query use the wizard, then base your report on this query.
Let me know if this fits the bill.

Reply | Quote