Single page report problems 1

[netrelik]

Hello everyone! My problem is that I am am creating a report that has some complicated totals. So I created a set of code that opens the report, then performs a set of sql queries to find each total. This procedure works very well if the report is greater than one page. However, if the page is only one page then the totals are blank. The code is working correctly but it's like the report needs to be refreshed or something. Any suggestions would be greatly appreciated!!

 

[dhookom]

We could make all kinds of guesses but without understanding your methods for totalling, we would probably be wasting time. Could you provide a little more detail?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[netrelik]

Sure...for example one of the totals that I do is the total of males and here is the code that I use. This is just one of the thirteen totals that is at the end of the report. Thanx!

'Number of males
Cmd1.CommandText = "select count([sex]) from profiles where [sex]='male' and [intake date] between #" & BD & "# and #" & ED & "#"
Set Rst1 = Cmd1.Execute
Report_Client_Demo.txtMales = Rst1.Fields(0)

 

[dhookom]

To count the number of males represented in a report's record source, use a text box with a control source of:
=Sum(Abs([Sex]="male&quot)
Does this work for you?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[netrelik]

Well...yes and no. You see the user wants to be able to set the date range for the report and some of the totals can get rather complicated. For example for the total of inactive clients:

'Total inactive clients
Cmd1.CommandText = "select count([pid]) from profiles where [intake date] between #" & BD & "# and #" & ED & "# and [date inactive] is not null"
Set Rst1 = Cmd1.Execute
Report_Client_Demo.txtInact = Rst1.Fields(0)

Is there no way to refresh data on a report like you can on a form with me.refresh or me.recalc? Thanx again for your help Duane!

 

[dhookom]

If the record source of your report (please respond yes or no) is from Profiles where the dates are between BD and ED then you should never create another recordset. Your inactive clients would be:
=Sum( IsNull([Date Inactive]) + 1)

IsNull([Date Inactive]) will evaluate to either True/-1 or False/0. Adding 1 will change the values to 0 or 1.

There is rarely any reason to create recordsets in reports. I would rather create a subreport than create recordsets in code.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[netrelik]

I was thinking that the criteria for each total was too complicated for the control source. For example I didn't know that you could do a sum of a filtered criteria. I am still having trouble with this one though. If you can walk me through this one then all my troubles will be fixed. Here is the sql statement I was using...it is computing the average age of females within a date range and they have to be active(not dead or in a nursing home). Thanx!!!

"select avg([age]) from profiles where sex='female' and [intake date] between #" & BD & "# and #" & ED & "# and isnull([date inactive])"

 

[dhookom]

Does your report have a record source? If so, what is it? Is the report opened with a where clause/filter? Or, is your report unbound with collection of unbound text boxes?

Did any of my previous suggestions work? Do you understand how they work? An equivalent expression for you latest response is:
=Sum(Abs([Sex]="Female" AND IsNull([Date Inactive]) ) )

All of this depends on your answers to my questions which you seem to have ignored. Without understanding how your report is set up, it is difficult to suggest solutions.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[netrelik]

I'm sorry...the record source for the report is a query that has all the necessary fields for the report. The query also has "Between [forms]![reports_form]![txtbdate] And [forms]![reports_form]![txtedate]" as the criteria for the [intake date]. The summary part of the report in the report footer is a bunch of unbound text boxes which at first was being set by code after the report was opened. However you have helped me set all the text boxes control sources, which I agree is a better method. The last one you helped me with, which I think is the most difficult, isn't quite what I need it to do. I need it to find the average age of only the active females. I am very grateful for your help Duane! You will get your much deserved credit!

 

[dhookom]

Sorry, I skipped the average part. Average is the same as the Sum() divided by the Count(). I gave you the Count() expression. The Sum() expression is
=Sum(Abs([Sex]="Female" AND IsNull([Date Inactive]) ) * [Age] )

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[netrelik]

Nope that don't work...it's needs to somehow be something like
=sum([age] where [sex]='female') / Sum(Abs([Sex]="Female&quot)

Any suggestions?

 

[dhookom]

Did you try combining the Sum and Count expression into a single control source? This is what I was suggesting:
[Blue]
=Sum(Abs([Sex]="Female" AND IsNull([Date Inactive]) ) * [Age] ) /Sum(Abs([Sex]="Female" AND IsNull([Date Inactive]) ) )
[/Blue]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[netrelik]

That's perfect! You have been a great help! You definately get a star! Thanx again!