Selecting Certain Number of Records - Query 1

[blumbra]

Hello,

I have a database that I'm writing that is handling a few tasks. One of its functions is adding new inventory (the final table gets exported to the POS system).

The price tags are all custom made and die cut on sheets with 18 labels.

During the actual inputing of the inventory I am going to have it so when 18 new records are added a msgbox comes up asking if you want to print a sheet. Thats easy.

Now if they don't want to print when they hit 18 and wait till they have (for example) 24 records with the "toPrint" field flagged I have my hangup. That makes a full sheet and a partial sheet. How do I query just 18 Records to pass to a report. Even better would be to take any amount divisible by 18.

I suppose I could create a temp table.

What do you all think?

-Bryan

 

[blumbra]

Ok, I found the "Top Values" setting under the properties of the query. I can easily slap an "18" in here...but...

what I would like to do though is get a recordcount and divide the result by 18. Then round down to the nearest whole number and multiply that by 18 to get the number of records I would actually want to print.

Make sense?

 

[dhookom]

You could add a text box to your Report Header section:
Name: txtCountAll
Control Source: =Count(*)
Visible: No
Add a text box to your Detail section:
Name: txtCount
Control Source: =1
Running Sum: Over All
Visible: No
Add code to the On Format event of the detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = (Me.txtCount > (Me.txtCountAll \ 18) * 18)
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[blumbra]

Excellent,

I quickly tried messing with that code. That will work great. The little "Cancel" bit is all I need to know to keep me running!

Thanks so much.

-Bryan