Limit query results/records?

[DevourU]

Is there a way to limit query results? Via form? TIA

-JS

 

[John Vinson]

Is there a way to limit query results? Via form? TIA

-JS

Yes. Use a Query with criteria, or set the form's Filter property.

For a more detailed answer please post a more detailed question.

John W. Vinson[MVP]

 

[Jeff Boyce]

"limit query results" ... ?

Do you mean limit the number of rows returned? Or limit the records to only
those meeting your criteria? Or limit the number of fields that get
returned? Or ...?

Kinda tough to offer specific suggestions to a general question.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Limit in what way? Do you mean criteria? If that is what you want then
create a form with unbound textbox to enter your criteria. Use macro or
event to open the query.
In the query use the text box of the form for criteria like --
[Forms]![YourForm]![YorTextBox]

If you are going to enter the first part then use this --
Like [Forms]![YourForm]![YorTextBox] & "*"

If you are going to enter the some part that may be anywhere in the field
then use this --
Like "*" & [Forms]![YourForm]![YorTextBox] & "*"

 

[DevourU]

Sorry 'bout that folks. I meant it literally. I want to return a certain
amount of records from a query. 4, 7, 14, whatever, but using TOP is static,
and I need to give it a variable. The example for this is every day a
certain amount of parts need to be counted, and this amount will change from
day to day. I just need to grab a count from a changing list.

TIA! Anyone working today?

 

[John Vinson]

Sorry 'bout that folks. I meant it literally. I want to return a certain
amount of records from a query. 4, 7, 14, whatever, but using TOP is static,
and I need to give it a variable. The example for this is every day a
certain amount of parts need to be counted, and this amount will change from
day to day. I just need to grab a count from a changing list.

TIA! Anyone working today?

Well, none of us "work" for the newsgroup. Everyone who posts here is
a volunteer, donating our time, even the occasional Microsoft
employee.

Where is this "count" stored? How can the query ascertain what today's
count might be?

You may need to actually construct the SQL of the query dynamically,
using VBA code.

John W. Vinson[MVP]

 

[DevourU]

Cool, glad you're here. I have the count calculated in a form. It's like
putting your hand in a candy jar and pulling out 6 pieces, but the candy is
from a make table without a uniqueID if we need one. I could do a table
append if it is the only way. I'll take anything ya got. Thankx for the
help.

-JS

 

[John Vinson]

Cool, glad you're here. I have the count calculated in a form. It's like
putting your hand in a candy jar and pulling out 6 pieces, but the candy is
from a make table without a uniqueID if we need one. I could do a table
append if it is the only way. I'll take anything ya got. Thankx for the
help.

I'm still not sure what you're counting. Do you want to display the
top N records in a Subform? export them to a text file? Just what do
you want to do?

GUESSING that you want to display records in a subform named subMyTop,
and that you have a textbox on the form named txtCount: you could use
the AfterUpdate event of txtCount (or the Click event of a button) to
run code like

Private Sub txtCount_AfterUpdate()
Dim strSQL As String
If IsNull(Me!txtCount) OR Me!txtCount < 0 Then Exit Sub
' don't display a null count
strSQL = "SELECT TOP " & Me!txtCount & " FieldA, FieldB" _
& " FROM sometable WHERE <criteria>;"
Me!subMyTop.RecordSource = strSQL
End Sub

John W. Vinson[MVP]

 

[DevourU]

Sorry 'bout that. I have a query to run and append an amount of records,
amount is calculated in a form. The query is sorted by descending cost. I
want to select the 6 most expensive. or 12, or 3 depending on what amount is
calculated in the form.
Better? Thankx for hangin in there for me.

-JS