473,225 Members | 1,310 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

How to limit a report to 6 records from many returned from query.

I have used a query and report filter to return records related to a specific account id. I want to print a report including only the latest 6 records out of the set returned by the record filter. I have set the sort order for the report on descending thus returning the latest records at the top of the report. However since there are well over 6 records related to the account number the report contains all the related records.

I have been trying to use a loop in the Report Format or Report Print areas to limit the printed records to the first 6 without success. I have also tried to enter a count loop in the report open without success. I have not used, nor have I figured out how to use the SQL statements yet.

I have defined a string as LOOKFOR in the Report open area and have assigned LOOKFOR to as below in the Report open area. The TestInfo string is the EsiID number which the filter uses to return the records related to the account number which is entered by the user.

'Construct the correct syntax for the
'Report Filter usage required.
LookFor = "EsiID = '" & TestInfo & "'"
'Turn on the filter and set it up
Me.FilterOn = True
Me.Filter = LookFor

I have yet to be able to figure out how to send on the first 6 records returned by the filter to the report for printing. It currently prints all records related to the account number. Some ideas would be appreciated.

I am fluent with VBA and have used it throughout my project so far with success.

Jul 27 '07 #1
1 1935
12,516 Expert Mod 8TB
For the query use SELECT TOP 6.
Jul 27 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

by: mark | last post by:
How do I get all fields on one page of a report? I have a report that has a column for each day of the week and 6 records for each day. I need each weekday's records returned on only one detail...
by: B Love | last post by:
I am wondering if I can have a dynamically generated report based upon the results of a query (in Access2000). Any ideas? Related to that (if that cannot be done) can the output of a query...
by: CSDunn | last post by:
Hello, I have a situation with MS Access 2000 in which I need to display report data in spreadsheet orientation (much like a datasheet view for a form). If you think of the report in terms of what...
by: Terri | last post by:
I have a form with a multi-select combo. I dynamically build a SELECT statement, open a report, and set the recordsource to my dynamic SELECT statement. I count the records returned in the report...
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
by: Mike Conklin | last post by:
This one really has me going. Probably something silly. I'm using dcount for a report to determine the number of different types of tests proctored in a semester. My report is based on a...
by: deko | last post by:
Is there any way to limit the number of records loaded into a ListBox? I looked at qdf.MaxRecords (to apply to the query that is the RowSource of the ListBox) but that only applies to ODBC data...
by: someone | last post by:
How can I limit the number of records a Query retrieves or at least limit the number of records returned on a report. I only want to print the top 5 or 10 returns of a query into a weekly reort....
by: aaron.reese | last post by:
Guys, this is my problem:- I have records which are linked by two fields on a left inner join (there may be 0,1 or more records in the right hand table) The relationship is not unique (it's...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.