New page after 15 records

[KHogwood-Thompson]

Hi All,

I have a report that lists payments to be made through the BACS system
direct with our bank. When I print the report, I need it to only have a
maximum of 15 records on each page with subtotals after each batch of 15
records. How can I achieve this easily??

 

[Duane Hookom]

It's a bit difficult to do this without understanding your table structure. I
would create a column in your report's record source query that returns a
grouping value. For instance if you wanted to group the Orders table in
Northwind into 15 orders based on the OrderID, your report's recordsource
would be:

SELECT Val(DCount("*","Orders"," OrderID <" & [OrderID]))\15 AS GroupNum,
Orders.*
FROM Orders
ORDER BY Orders.OrderID;

In your report, you would set up a sorting and grouping level on GroupNum.

 

[KHogwood-Thompson]

Thanks for the information, I have created a query to test the sql and the
query is as follows:

SELECT Val(DCount("*","Payroll Employees Pay Slips"," EMPLOYEE REF<" &
[EMPLOYEE REF]))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

where the underlying table is called Payroll Employees Pay Slips, and the
field that I want to limit the records to 15 is called EMPLOYEE REF

I get a syntax error when I run the query and the GroupNum column displays
the #Error code. Is there something that I am missing here?
--
K Hogwood-Thompson

It's a bit difficult to do this without understanding your table structure. I
would create a column in your report's record source query that returns a
grouping value. For instance if you wanted to group the Orders table in
Northwind into 15 orders based on the OrderID, your report's recordsource
would be:

SELECT Val(DCount("*","Orders"," OrderID <" & [OrderID]))\15 AS GroupNum,
Orders.*
FROM Orders
ORDER BY Orders.OrderID;

In your report, you would set up a sorting and grouping level on GroupNum.
--
Duane Hookom
Microsoft Access MVP

Hi All,

I have a report that lists payments to be made through the BACS system
direct with our bank. When I print the report, I need it to only have a
maximum of 15 records on each page with subtotals after each batch of 15
records. How can I achieve this easily??

 

[Duane Hookom]

Your issue is probably caused by spaces in your field and table names. If
[EMPLOYEE REF] is numeric, try:
SELECT Val(DCount("*","Payroll Employees Pay Slips","[EMPLOYEE REF]<" &
[EMPLOYEE REF]))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

If it is text, try:
SELECT Val(DCount("*","Payroll Employees Pay Slips","[EMPLOYEE REF]<""" &
[EMPLOYEE REF] & """"))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

--
Duane Hookom
Microsoft Access MVP

Thanks for the information, I have created a query to test the sql and the
query is as follows:

SELECT Val(DCount("*","Payroll Employees Pay Slips"," EMPLOYEE REF<" &
[EMPLOYEE REF]))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

where the underlying table is called Payroll Employees Pay Slips, and the
field that I want to limit the records to 15 is called EMPLOYEE REF

I get a syntax error when I run the query and the GroupNum column displays
the #Error code. Is there something that I am missing here?
--
K Hogwood-Thompson

It's a bit difficult to do this without understanding your table structure. I
would create a column in your report's record source query that returns a
grouping value. For instance if you wanted to group the Orders table in
Northwind into 15 orders based on the OrderID, your report's recordsource
would be:

SELECT Val(DCount("*","Orders"," OrderID <" & [OrderID]))\15 AS GroupNum,
Orders.*
FROM Orders
ORDER BY Orders.OrderID;

In your report, you would set up a sorting and grouping level on GroupNum.
--
Duane Hookom
Microsoft Access MVP

Hi All,

I have a report that lists payments to be made through the BACS system
direct with our bank. When I print the report, I need it to only have a
maximum of 15 records on each page with subtotals after each batch of 15
records. How can I achieve this easily??

 

[KHogwood-Thompson]

Thanks for that Duane, not sure if that was the problem or not, as I get the
following error when I run that SQL:

"The expression that you entered as a query parameter produced this error:
'The object doesn't contain the Automation object 'CO?' "

The underlying query "Payroll Employees Pay Slips" contains two Integer
parameters, [CO?] and [WEEK NO?], basically selecting the company number and
the payroll week.
--
K Hogwood-Thompson

Your issue is probably caused by spaces in your field and table names. If
[EMPLOYEE REF] is numeric, try:
SELECT Val(DCount("*","Payroll Employees Pay Slips","[EMPLOYEE REF]<" &
[EMPLOYEE REF]))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

If it is text, try:
SELECT Val(DCount("*","Payroll Employees Pay Slips","[EMPLOYEE REF]<""" &
[EMPLOYEE REF] & """"))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

--
Duane Hookom
Microsoft Access MVP

Thanks for the information, I have created a query to test the sql and the
query is as follows:

SELECT Val(DCount("*","Payroll Employees Pay Slips"," EMPLOYEE REF<" &
[EMPLOYEE REF]))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

where the underlying table is called Payroll Employees Pay Slips, and the
field that I want to limit the records to 15 is called EMPLOYEE REF

I get a syntax error when I run the query and the GroupNum column displays
the #Error code. Is there something that I am missing here?
--
K Hogwood-Thompson

It's a bit difficult to do this without understanding your table structure. I
would create a column in your report's record source query that returns a
grouping value. For instance if you wanted to group the Orders table in
Northwind into 15 orders based on the OrderID, your report's recordsource
would be:

SELECT Val(DCount("*","Orders"," OrderID <" & [OrderID]))\15 AS GroupNum,
Orders.*
FROM Orders
ORDER BY Orders.OrderID;

In your report, you would set up a sorting and grouping level on GroupNum.
--
Duane Hookom
Microsoft Access MVP


:

Hi All,

I have a report that lists payments to be made through the BACS system
direct with our bank. When I print the report, I need it to only have a
maximum of 15 records on each page with subtotals after each batch of 15
records. How can I achieve this easily??

 

[Duane Hookom]

IMHO, don't ever use parameter prompts in queries. Take a look at
http://www.tek-tips.com/faqs.cfm?fid=6763 and replace your criteria with
references to controls on a form.
--
Duane Hookom
Microsoft Access MVP

Thanks for that Duane, not sure if that was the problem or not, as I get the
following error when I run that SQL:

"The expression that you entered as a query parameter produced this error:
'The object doesn't contain the Automation object 'CO?' "

The underlying query "Payroll Employees Pay Slips" contains two Integer
parameters, [CO?] and [WEEK NO?], basically selecting the company number and
the payroll week.
--
K Hogwood-Thompson

Your issue is probably caused by spaces in your field and table names. If
[EMPLOYEE REF] is numeric, try:
SELECT Val(DCount("*","Payroll Employees Pay Slips","[EMPLOYEE REF]<" &
[EMPLOYEE REF]))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

If it is text, try:
SELECT Val(DCount("*","Payroll Employees Pay Slips","[EMPLOYEE REF]<""" &
[EMPLOYEE REF] & """"))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

--
Duane Hookom
Microsoft Access MVP

Thanks for the information, I have created a query to test the sql and the
query is as follows:

SELECT Val(DCount("*","Payroll Employees Pay Slips"," EMPLOYEE REF<" &
[EMPLOYEE REF]))\15 AS GroupNum, [Payroll Employees Pay Slips].*
FROM [Payroll Employees Pay Slips]
ORDER BY [Payroll Employees Pay Slips].[EMPLOYEE REF];

where the underlying table is called Payroll Employees Pay Slips, and the
field that I want to limit the records to 15 is called EMPLOYEE REF

I get a syntax error when I run the query and the GroupNum column displays
the #Error code. Is there something that I am missing here?
--
K Hogwood-Thompson


:

It's a bit difficult to do this without understanding your table structure. I
would create a column in your report's record source query that returns a
grouping value. For instance if you wanted to group the Orders table in
Northwind into 15 orders based on the OrderID, your report's recordsource
would be:

SELECT Val(DCount("*","Orders"," OrderID <" & [OrderID]))\15 AS GroupNum,
Orders.*
FROM Orders
ORDER BY Orders.OrderID;

In your report, you would set up a sorting and grouping level on GroupNum.
--
Duane Hookom
Microsoft Access MVP


:

Hi All,

I have a report that lists payments to be made through the BACS system
direct with our bank. When I print the report, I need it to only have a
maximum of 15 records on each page with subtotals after each batch of 15
records. How can I achieve this easily??