... (1 Viewer)

moke123

AWF VIP
Local time
Today, 14:02
Joined
Jan 11, 2013
Messages
3,852
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_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:02
Joined
Feb 28, 2001
Messages
26,999
The union sub-queries have to match in both number of fields and data type, I believe.
 

plog

Banishment Pending
Local time
Today, 13:02
Joined
May 11, 2011
Messages
11,611
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:02
Joined
May 7, 2009
Messages
19,169
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

  • DB (2).zip
    105.6 KB · Views: 400

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:02
Joined
May 7, 2009
Messages
19,169
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

  • DB (2).zip
    38.1 KB · Views: 409

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:02
Joined
May 7, 2009
Messages
19,169
see Query1 in design view.
it has a Criteria to only show records for This month, and This Year.
 

HealthyB1

Registered User.
Local time
Tomorrow, 04:32
Joined
Jul 21, 2013
Messages
96
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

  • DB3.accdb
    716 KB · Views: 305

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:02
Joined
May 7, 2009
Messages
19,169
see this.
 

Attachments

  • DB(3).zip
    76.3 KB · Views: 408

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:02
Joined
May 7, 2009
Messages
19,169
did you really saw it, have you downloaded it and tried? my upload is different from HealthyB1.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:02
Joined
May 7, 2009
Messages
19,169
if you download it, then you did not noticed that Each month is in separate page?
rpt_page1.png
rpt_page2.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:02
Joined
May 7, 2009
Messages
19,169
it's ok. you will noticed in the code, i used 36 for intLinesPerPage.
try to adjust this if you see "unusual" extra lines.
 

HealthyB1

Registered User.
Local time
Tomorrow, 04:32
Joined
Jul 21, 2013
Messages
96
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: 129

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:02
Joined
May 7, 2009
Messages
19,169
you manually "count" the number of lines (on the screen), for the "full page report".
then substitute that number (minus 1) to intLinesPerPage.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:02
Joined
May 7, 2009
Messages
19,169
i updated the code.
you can test again with another "scenarios".
 

Attachments

  • db_new.zip
    192.8 KB · Views: 125

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:02
Joined
May 7, 2009
Messages
19,169
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

  • DB (1) (1).zip
    59.9 KB · Views: 119

Users who are viewing this thread

Top Bottom