Have I stumped the entire newsgroup???

  • Thread starter Thread starter ABW
  • Start date Start date
A

ABW

Or did I not make myself entirely clear????
ABW



I've duplicated a mileage expense claim form used in our organization. I've
created a query to extract the dataset of records for any given month.

I am having trouble making the report calculate the number of miles based
upon the number of records on a page. I've created a MileageID footer with
a calculated text box. I'd like that text box to print as the 22nd line of
the report. If there are fewer than 21 detail records, the calculated text
box can print after the last record but if there are more than 21 detail
records, I'd like the calculated text box to calculate the number of miles
for that page and then go to a new page, again, summing on or before the
21st detail record.

I've set the Sorting and Grouping properties of the footer to INTERVAL with
a Group Interval of 21. The report, however seems to print the calculated
text box anywhere except where I think I've told it to print.

Any help offered is GREATLY appreciated.

Anthony B. Washington
Sr. Systems Analyst
---> All of life is learning <---
 
I would create a calculated sequence field in the query. This would number
each record from 1 through whatever. You can then integer divide your
sequence by 21 to get groups of 21 records. Use this grouping in your
report's sorting and grouping.

Without knowing your data, I can't be of much more assistance.
 
Thank you for the reply.

What information about my data can I give you so you can give me specifics
about the calculated sequence field? Further, would you be able to assist me
with the integer divide? I've looked up both calculated sequence field and
integer divide in the help file and nothing specific comes up.

Would the solution you are thinking of consist of VB programming or just the
Access front end?

Thanks.

ABW
 
Hi
Implementing Duane's idea, I would add 3 columns on the query used for the
report.

- Var1. Set this variable to 1

- VarSeq. This will be a running total of Var1, therefore, effectively
becomes the *calculated sequence field*. You would need to do a search on
Google for "running total" or "running balance" for the logic of this field
(unless you already know it).

- VarReportBreak. This is VarSeq / 21. This is the *integer divide* to
determine whether or not the report should break and do a subtotal. If you
force VarReportBreak to be of integer type, the value of this variable
changes every 21 records.


The *calculated sequence field* and *integer divide* are not something you
would find under Help or any other documentation. They are part of the art
of programming, I guess <g>. I'm an accountant by trade but have been
programming in Access off and on the last 3 yrs. It is truly an art!


HTH
Immanuel Sibero




ABW said:
Thank you for the reply.

What information about my data can I give you so you can give me specifics
about the calculated sequence field? Further, would you be able to assist me
with the integer divide? I've looked up both calculated sequence field and
integer divide in the help file and nothing specific comes up.

Would the solution you are thinking of consist of VB programming or just the
Access front end?

Thanks.

ABW
Click to expand...
 
The following query was created in the Northwind.mdb
SELECT DCount("*","Customers","CustomerID <""" & [CustomerID] & """")\21 AS
Seq, Customers.*
FROM Customers;
The Seq column numbers 21 records with a 0, 21 records with 1,... Set your
primary sorting and grouping level will be [Seq] and then CustomerID. Set
the seq grouping to display a footer where you can do totals and/or page
breaks.

--
Duane Hookom
MS Access MVP


ABW said:
Thank you for the reply.

What information about my data can I give you so you can give me specifics
about the calculated sequence field? Further, would you be able to assist me
with the integer divide? I've looked up both calculated sequence field and
integer divide in the help file and nothing specific comes up.

Would the solution you are thinking of consist of VB programming or just the
Access front end?

Thanks.

ABW
Click to expand...
 
Back
Top