471,118 Members | 996 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,118 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 1805
12,516 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

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.