By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 801 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Only print last detail line for primary key, suppress all others

P: n/a
Hi,

I have a report that has a query as its record source.
The query picks up records from a table that meet certain criterion.
The report then counts how many records there are for each key (room
number) - but does some calculating by date to see whether to increment
the count.

Then the report lists the Room Number, and the Count.
The way it is now, if the count for a room number is 4, it will
display:
Room Count
-------- --------
200 1
200 2
200 3
200 4

What I'd like to do is ONLY print the last line.

I don't think I can do this via my query, because Count is not on my
table and is calculated in my report's VBA. The logic I use is: if
one record and the next have an ActivityDate within 11 days of each
other, the count is 1. If it is over 11 days, the count is 2. And so
on with the next record, etc. In my VBA, I check record-by-record. I
don't think this logic can be done in a query, can it?

Is there a way to do this, either by my report (and only print the last
detail line for a room number), or by query?

Thanks so much for any help!
Lori

Oct 16 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
lorirobn wrote:
Hi,

I have a report that has a query as its record source.
The query picks up records from a table that meet certain criterion.
The report then counts how many records there are for each key (room
number) - but does some calculating by date to see whether to increment
the count.

Then the report lists the Room Number, and the Count.
The way it is now, if the count for a room number is 4, it will
display:
Room Count
-------- --------
200 1
200 2
200 3
200 4

What I'd like to do is ONLY print the last line.

I don't think I can do this via my query, because Count is not on my
table and is calculated in my report's VBA. The logic I use is: if
one record and the next have an ActivityDate within 11 days of each
other, the count is 1. If it is over 11 days, the count is 2. And so
on with the next record, etc. In my VBA, I check record-by-record. I
don't think this logic can be done in a query, can it?

Is there a way to do this, either by my report (and only print the last
detail line for a room number), or by query?

Thanks so much for any help!
Lori
Yes, it can be done. But you'll need to know how to program. See
"MoveLayout, NextRecord, PrintSection Properties" in help or create a
code mode and enter/highlight the word MoveLayout and press the F1 key
to get to the help topic.

You'd probably need to create a group on Room. Have a global variable
the is reset when a new room is found. You'll need to know how the
count of the rooms per group. Then you simply move to the next record,
but the MoveLayout and PrintSections set to false if not equal to the
last room record.

Or, in the recordsource query of the report call a function that returns
a true or false on whether or not the record is to be printed.
PrintRec : DoIPrintIt([Room], [RoomRecID])
and write a public function to return true or false to print.

Oct 16 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.