...

jazsriel

Member
Local time
Today, 09:29
Joined
Dec 21, 2020
Messages
65
...
 
Last edited:
You have the following union query in your report module
Code:
strSQL = strSQL & "select * from Query1 "
    strSQL = strSQL & "Union "
    strSQL = strSQL & "select Top " & intPad & " * from dummy;"
They apparently dont have the same number of fields (columns).

With a union query if the first query must have the matching number of fields as the second query. If need be you can always add a dummy column to one of the queries.
 
Last edited:
The union sub-queries have to match in both number of fields and data type, I believe.
 
You need to fix your table before proceeding.

1. 'Date' is a reserved word and shouldn't be used for a field name. Instead you should prefix it with what that data represents (InventoryDate, SalesDate, etc.)

2. For numeric data, you should store it as a numeric field type. NoUnits, Loss, and TotalLoss should not be Short Text.

3. Use better names all around. You've just accepted the generic prompts from Access for your object names--Table1, Query1, Form1, Report1.
 
here i revisited your db again.
i added the 3 new fields in dummy table.
i added another criteria to Query1 to show only "Current month" (you already have the "current year").
 

Attachments

you're welcome.
again i check your Query1, it seems you don't need the 3 additional columns.
you can set them on the report (see the Control Source of each "Expr" textbox).
 

Attachments

see Query1 in design view.
it has a Criteria to only show records for This month, and This Year.
 
Try this. I haven't put them on separate pages but they are in separate sections so you should be able to proceed from here
 

Attachments

see this.
 

Attachments

did you really saw it, have you downloaded it and tried? my upload is different from HealthyB1.
 
if you download it, then you did not noticed that Each month is in separate page?
rpt_page1.png
rpt_page2.png
 
it's ok. you will noticed in the code, i used 36 for intLinesPerPage.
try to adjust this if you see "unusual" extra lines.
 
Hi Arnelgp,
FWIW This is what I am seeing from your latest version. There is no multiple pages with separate heading for each month.
 

Attachments

  • Arnelgp.PNG
    Arnelgp.PNG
    78 KB · Views: 179
you manually "count" the number of lines (on the screen), for the "full page report".
then substitute that number (minus 1) to intLinesPerPage.
 
i updated the code.
you can test again with another "scenarios".
 

Attachments

the report is Report1.
i created a Temporary table, zzTable.
the Report, Report1 is using this table as the Recordsource.
see the Open event of Report1.
 

Attachments

Users who are viewing this thread

Back
Top Bottom