Help with Report

Hello




I'm building a report based on all Category Name. The report would include category name as column heading and the rest of the data as row heading. I have built the query but not sure if i'm going about it the right way. QueryReport1 is a union query which put all category names into one column. QueryReport2 is a crosstab query which puts the entire category into column heading. Not sure why but queryreport2 returns a blank row. Take a look at reports to get a better idea of how the report would look. I have attached a copy of the db. Any suggestions are more than welcome. Thank You.

Copy (2) of 720.mdb

The report is not named R_TransactionsDadReport, it is named Report2 so the code immediately errors trying to open report that doesn't exist.

Report setup is too wide for legal paper. I presume you intend to change rest of the labels to vertical and resize textboxes.

The crosstab returns a blank row because there are records in the UNION with no data for Description, TransactionType, CheckNumber, DepositAmount, WithdrawalAmount fields for categories Insurance, Legal Fee, Materials and Repairs, Miscellaneous.

You can apply a filter to the report to exclude those rows.

DoCmd.OpenReport "Report2", , , "Not IsNull([Description])"

I will try that. Otherwise you think both query are right?? I try to rest the labels vertical but they still don't fit. Not sure what I'm going to do.

Queries look fine.

I did some playing around with setting labels vertical and resizing the textboxes. An option is to display the data on two lines. It can be tedious to get report design just right but be creative with arrangement and adjust control sizes to the minimum required. For instance, date textbox with this font size and format probably doesn't need to be wider than 0.8333". How big can the dollar amounts be? Maybe don't display dollar sign. Maybe not all need decimals displayed.

dollar amounts would be in the thousands (10,000.00). Not displaying dollar sign is a great idea. How would i go about displaying data on two lines????

Arrange the textboxes above each other, in a staggered alignment. Can do the same with labels, vertical or not. This is a solution I have used for very wide reports.

I try ​DoCmd.OpenReport "Report2", , , "Not IsNull([Description])", acViewPreview. Something is off. It try's to print and opens a new window to save file as .xps. The report doesn't open at all.

You have the acViewPreview in the wrong place.

DoCmd.OpenReport "Report2", acViewPreview, , "Not IsNull([Description])"

I fixed the code but something else is off. I never gotten this error before. The Microsoft Jet database engine does not recongnize '[Forms]![F_TransactionsDadReportForm]![servicefrom]' as a valid field name or expression. I checked the name field and its right.

Why have two forms for entering From and To date range criteria? One can serve this purpose for all reports.

Post the entire code procedure or revised db.

Between [Forms]![F_TransactionsDadReportForm]![servicefrom] And [Forms]![F_TransactionsDadReportForm]![serviceto]

Not sure how to go about using one form for both reports

Is that expression criteria in query? Provide full sql statement.

I have to be able to reproduce the error to analyse. So either post all code and sql or provide the revised db with these objects.

The two forms in the db version I have are identical. Each has two textboxes for date range entry and two command buttons. Just that the OK button on each form is programmed for a specific report. Instead of a form for every report created, offer multiple buttons on one form to open desired report.

Here is the copy of the db

720 - Copy.mdb

Maybe the issue is CROSSTAB query. I don't have any reports based on CROSSTAB. I also seldom use dynamic parameters in query. I use WHERE CONDITION argument of DoCmd.OpenReport/OpenForm. Can't get the query parameter to work but this does:

DoCmd.OpenReport "R_TransactionsDadReport", acViewPreview, , "Not IsNull([Description]) AND TransactionDate Between #" & Me.servicefrom & "# AND #" & Me.serviceto & "#"

Thank you for your reply. I haven't been able to work on this because i been without power. Just got power back.
I try the code and it give me this error
Run-time Error 3070
The Microsoft Jet database engine does not recongnize Forms!F_TransactionsDadReportForm!servicefrom' as a valid field name or expression.