468,766 Members | 1,408 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,766 developers. It's quick & easy.

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.

Thanks!
Jul 27 '07 #1
1 1731
Rabbit
12,515 Expert Mod 8TB
For the query use SELECT TOP 6.
Jul 27 '07 #2

Post your reply

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

Similar topics

6 posts views Thread by B Love | last post: by
1 post views Thread by Nathan Bloomfield | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.