Home  |  About  |   Search   

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

RunCommand Constants

Terms of Use


 

Queries: Have a query return every nth record in a table

Author(s)
Erika Yoxall

(Q)    How can I write a query to return every fifth record from a table?

(A)     Here's a general-purpose way to write a query to return every nth record from a table. It will choose equally-spaced records, but not start in any particular spot, i.e. it might return the 1st, 5th and 9th or 2nd, 6th and 10th records if set to a spacing of 4. First, create a new function:

Function PlusOne (var As Variant)
Static i As Integer
    i = i + 1
    PlusOne = i
End Function

Then, create your query. Drag down whatever fields you want to see in the output. Add one more column to the query, with these properties:

Field Expr1: PlusOne([MyField]) Mod 5
Show No
Criteria 0

You can use any field in your output in place of MyField -- it doesn't matter which one you use. If you want every 7th record, use Mod 7, for every 10th record, use Mod 10, and so on.


1998-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer