Dlookup Printing / previewing problem

[MorningSun]

I don't know if this is a dlookup problem or something else.

I have a main report that has 3 subreports on it. In the footer of each subreport I have used DLookup functions to get totals for different regions (Southeast, midwest, northeast) that will be used for totals on the main report.

ChattCSTotals:
=(DLookUp("nz(Sum([NEPoints1]+[NEPoints2]))+nz(Sum([EoiPoints]))","qrySAAuditTotalsOrgFLEX","[SAlocation]='chattanooga'")

On the main report I reference the text boxes containing these dlookup functions to do more calculations. (ChattanoogaTotal= [subform1]![chattCStotals]+[subform2]![chattSAtotals] etc....)

When I preview the report (and all subreports)everything is perfect. All the numbers are there and they are all correct HOWEVER when I actually print the report, MOST of the calculated fields in the Main report and the subreport footer's become #Error.

Any ideas? Is this a dlookup problem, is it the amount of memory my form utilizes? What is "print" doing that "print preview" is not?

Thanks so much!!!!!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[dhookom]

I'm not sure why you would use such a complex expression. Couldn't your query do all of this calculation since the where clause is hard-coded. I assume all of these expressions are in Control Source properties and you are not using any code in this report.

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

 

[CosmoKramer]

Does the report work if you print it directly without previewing??

If so, it looks like you've stumbled across a known problem:

http://support.microsoft.com/default.aspx?scid=kb;en-us;326453&Product=acc97

Hoc nomen meum verum non est.

 

[MorningSun]

The expression is so complex because the report itself holds 4 main subreports, each showing the results of a different Job title. Unfortunately my database is not normalized and therefore my queries are too complex to do all of the calculations themselves. I have hit maximums on almost everything and therefore I have had to nest subforms based on subqueries in order to get all of the numbers to calculate properly. The queries perform many of the calculations and the first level of subreports(the Job title level subreport) does another level of calculations, some of which are based on second level subreports and queries.

I need the same totals based on 2 different locations (Chattanooga and Portland) On the main report. This is not a problem with the subreports. They work perfect and divide Chatt and Portland numbers. The problem comes where on the Main Report that the subreports are nested in, I need to get the totals across all job titles and therefore perform calculations based on different locations across different subreports. In order to divide the totals across locations, I used dlook up in the subreport footers to get the overall totals for each location and the main report pulls those numbers. I couldn't get the main report to divide between the 2 locations otherwise. Have I missed something? I'm in over my head!!

As for printing the report without previewing, how would you do that? When you open a report, it is in preview mode (or design mode). I tried one of the solutions in the article by putting it on view 2 pages but it still didn't work.

Here is the most complex of the queries for one of the subreports. As you can see, it's based on subqueries already as getting all of the totals in one query produced either "too many fields defined" or "query too complex" errors. I am using 97.

SELECT tblCSAuditFLEX.AuditNo, tblCSAuditFLEX.Quarter, tblCSAuditFLEX.Location, tblCSAuditFLEX.Manager, tblCSAuditFLEX.AuditType, Format([assessmentdate],"yyyy") AS Year, qryCSAuditTotalsSubFLEX.FileMaintenanceAudited, qryCSAuditTotalsSubFLEX.FileIncorrect, qryCSAuditTotalsSubFLEX.GSSAudited, qryCSAuditTotalsSubFLEX.GSSIncorrect, qryCSAuditTotalsSubFLEX.AgreementAudited, qryCSAuditTotalsSubFLEX.AgreementIncorrect, qryCSAuditTotalsSubFLEX.MagicAudited, qryCSAuditTotalsSubFLEX.MagicIncorrect, qryCSAuditTotalsSubFLEX.DataAudited, qryCSAuditTotalsSubFLEX.DataIncorrect, qryCSAuditTotalsSubFLEX.IRIMSAudited, qryCSAuditTotalsSubFLEX.irimsTransfer, qryCSAuditTotalsSubFLEX.GPCAudited, qryCSAuditTotalsSubFLEX.GPCIncorrect, qryMerlinAuditedFLEX.MerlinAudited, qryCSAuditTotalsSubFLEX.BG00Audited, qryCSAuditTotalsSubFLEX.BG00Incorrect, qryCSAuditTotalsSubFLEX.NCAudited, qryCSAuditTotalsSubFLEX.NCIncorrect, tblCSAuditFLEX.Reviewer
FROM (tblCSAuditFLEX LEFT JOIN qryCSAuditTotalsSubFLEX ON tblCSAuditFLEX.AuditNo = qryCSAuditTotalsSubFLEX.AuditNo) LEFT JOIN qryMerlinAuditedFLEX ON tblCSAuditFLEX.AuditNo = qryMerlinAuditedFLEX.AuditNo
GROUP BY tblCSAuditFLEX.AuditNo, tblCSAuditFLEX.Quarter, tblCSAuditFLEX.Location, tblCSAuditFLEX.Manager, tblCSAuditFLEX.AuditType, Format([assessmentdate],"yyyy"), qryCSAuditTotalsSubFLEX.FileMaintenanceAudited, qryCSAuditTotalsSubFLEX.FileIncorrect, qryCSAuditTotalsSubFLEX.GSSAudited, qryCSAuditTotalsSubFLEX.GSSIncorrect, qryCSAuditTotalsSubFLEX.AgreementAudited, qryCSAuditTotalsSubFLEX.AgreementIncorrect, qryCSAuditTotalsSubFLEX.MagicAudited, qryCSAuditTotalsSubFLEX.MagicIncorrect, qryCSAuditTotalsSubFLEX.DataAudited, qryCSAuditTotalsSubFLEX.DataIncorrect, qryCSAuditTotalsSubFLEX.IRIMSAudited, qryCSAuditTotalsSubFLEX.irimsTransfer, qryCSAuditTotalsSubFLEX.GPCAudited, qryCSAuditTotalsSubFLEX.GPCIncorrect, qryMerlinAuditedFLEX.MerlinAudited, qryCSAuditTotalsSubFLEX.BG00Audited, qryCSAuditTotalsSubFLEX.BG00Incorrect, qryCSAuditTotalsSubFLEX.NCAudited, qryCSAuditTotalsSubFLEX.NCIncorrect, tblCSAuditFLEX.Reviewer
HAVING (((tblCSAuditFLEX.Quarter) Like [forms]![frmselectreportOrg]![quarter]) AND ((tblCSAuditFLEX.AuditType) Like [forms]![frmselectreportOrg]![type]) AND ((Format([assessmentdate],"yyyy")) Like [forms]![frmselectreportOrg]![year]))
ORDER BY tblCSAuditFLEX.AuditNo, tblCSAuditFLEX.Quarter, tblCSAuditFLEX.Location, tblCSAuditFLEX.Manager, tblCSAuditFLEX.AuditType;

Is it hopeless?????

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[CosmoKramer]

Dawn,

In the On Click event of a button on a form, add this statement, of course changing the report name:

DoCmd.OpenReport "YourReportName", acViewNormal

This will print the report to your default printer.....

Hoc nomen meum verum non est.

 

[MorningSun]

Hey, that worked! BUT I want them to be able to look at it before printing it. Why would it work this way but not the other way?? Is there some way that I could allow them to preview it but make it print as acviewnormal?

THANKS!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[CosmoKramer]

The link I gave shows this as the cause:

You are programmatically calculating your totals. The totals are calculated by using a report event that fires per page. Each page must be previewed to fire that event for that page and then calculate the correct totals.

Would it be possible to have two command buttons, one to preview and one to print?? Or, you could try Method 3, Scroll Through All Pages of the Report as quick fixes.....

Hoc nomen meum verum non est.

 

[MorningSun]

It's just odd that I have SEVERAL reports and this is the only one that it occurs on. I can put a print and a preview button but some of the people I work with just won't get it. I'm trying to make everything as consistent as possible. 2 different buttons is much better than re-building this monster!!

We will be converting to Windows XP in the next few months. I'll have to convert all of my databases to Access 2000 at that time. Do you think that this will this solve the problem?

Thanks so much for your help!!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[CosmoKramer]

You're welcome

The MS page says that the problem is fixed in Access XP...

Hoc nomen meum verum non est.