Ok All,

Here's the deal. I've have to figure out some logic that will basically only return every fifth record from a table. I have two fields in this table New Sales Calls and Reorder Calls. They are number fields. Basically, what I want the query to do is look at these two fields and determine starting from record one for that particular agent through to the last record for that particular agent and only return every fifth record. Basically, for each record there will be one new sales call or one reorder call. There won't be a time when they are the same. So both fields have to be taken into consideration. In other words my first two records might be new sales and the next three will be reorders. The fifth record in this case would pull the reorder since it's the fifth record. I hope I'm making sense. Thanks for any help.

Try this page.

hope this helps
-AJ

Thanks. But where would I define the function, in the query itself, or in the module area of the database window?

If you're using that method, you would create that function in a module, not within the query itself.

-AJ

Ok, I got that part now. Now the part about where it says in the article that it might not start in any particular spot. Is there a way around this? I want it to start from the first record and then pull every 5th thereafter. It needs to be consistent. Thanks.

Remove this line from the function
Static i as Integer

Outside of any procedure in a standard module add the line
Public i as Integer

Now, presumably this is used for a report, right? and if so, you probably have a command button opening the report. add one line of code to that command button's On Click event, BEFORE the line of code that opens the report.

i = 0

I didn't test this. You may have to play around with it to get it to work.
One other thing, change the name of i. One-letter variable names are really bad form. I didn't notice that's the author had written when I posted the link.

-AJ

Actually,

I'm not using a report. I just want to use the query. Since I'm not using a report, where would I set i = 0? Thanks.

You have to do it before you run the query. If the query is not run by any sort of code (i.e. you're just dbl-clicking on it in the database window), you'll have to open the Visual basic window and just type
i = 0
in the Immediate window (press F6 if you don't see it) and then hit enter.

-AJ

Ok, but where at in the Visiual Basic Window.

If you're running the query by double-clicking it in the database window, you'll have to execute that line of code in the Immediate Window in Visual Basic.

If the Immediate window does not appear in your visual basic environment, you can make it appear by hitting Ctrl-G. I said F6 before. I was wrong.

Ok. Here's another issue. This has to be done for each employee. In other words I have 5 employees that are assigned agents. The numbering doesn't take into consideration that when it goes down the list that when it comes up to the second employee that the numbering has to start over. Is there any way to have some logic tell the counter ok now here's a new employee start the count over. Thanks.