Is it a very dumb question ?

[Alvis]

Question 1:

I'm using MS access to write a small app for my friend. I need to display and print the first 'X' number of rows in a select query based on a number the users enters in a form. Can you help?
​

Question 2:

Is there a way to restrict the number of rows that are returned from a select query? My select query comes back with 30,000 rows, and I just want the first 300.
I used the query property to update the number of values to be returned (Query design view, Property sheet: Query properties, Top values). How can I automate this through getting the number of records needed through a user form? Any other way available ?
​

 

[John Big Booty]

You can use the Return drop down menu to set either a set number of records to return or a percentage of total records to return.

 

[Alvis]

Dear John ... thanks for the reply.
If I have to ask the user for the number of records to be displayed through an user form and then I have to use that value to display the number of records in a select query. How to do that ?

 

[Steve C]

Hello Alvis, JBB's solution is perfect, however, might it be you want the user to decide how many records he wants?

if yes, here's how I might do it

Create a query with column to count the records

set the criteria for that column to read off your user's form Text Box

I haven't got the precise text to acheive that off the top of my head - but I'm confident I could write it - if it's what you need. If it's not what you need, I've saved the effort.

 

[Alvis]

Dear Steve, yes that's exactly what I want. In the form, User will type in the number of records he/she wishes to see in the select query. Based on that input, I wish the select query to display the records.

By the method you have explained .... the count of records field in the output of select query should read 1,2,3,4.... and then I can set the criteria to restrict the records to a particular number based on the input by user thru' the form.

But I need help to set up that query to show the count of records ... need help here ...

Please support.

- Alvis.

 

[Alvis]

Dear Steve, yes that's exactly what I want. In the form, User will type in the number of records he/she wishes to see in the select query. Based on that input, I wish the select query to display the records.

By the method you have explained .... the count of records field in the output of select query should read 1,2,3,4.... and then I can set the criteria to restrict the records to a particular number based on the input by user thru' the form.

But I need help to set up that query to show the count of records ... need help here ...

Please support.

- Alvis.

 

[Steve C]

Righto Alvis, I'll work something up, and reply tomorrow.

 

[Steve C]

First of all, there are some big hitters on here that know a very lot more about access than me - I might only be one page ahead (or behind) Alvis in the manual, but please don't have a pop at me for an inelegant solution. It's my best effort.

There I've apologised in advance.

Here goes then

Table1 is your data & looks like this

Field1ID
Field2

Notes:
1) Field1ID is an auto number PK field (doesn’t matter if some PK are deleted)
2) In my database Feild2 of every record is 1 (it’s a FK, I know it’ll never change) so unless you add a field with the number 1 in every record my method won’t work for you (it’s easy to add a field and make every record 1with an update query also set default to 1).
3) you can have whatever other fields you like in Table1 (Plus your other fields)

Query1 has three columns in the QBE Grid

Field: MyField1ID:Field1ID
Table: Table1
Total: Group By
Sort: LEAVE BLANK
Group By

Note: I have added an alias to the Field1ID above and called it MyField1ID: That’s so I can use MyField1ID and Field1ID in MyExpr below.

Field: Field2
Table: Table1
Sort: LEAVE BLANK
Total: Group By

Field: MyExpr: DSum(“Field2”,”Table1”,”Field1ID<=”[MyField1ID])
Table: LEAVE BLANK
Total: Expression
Sort :LEAVE BLANK

This query will number each line but MyExpr: comes back as a string not a number. I don’t know how to fix that right now, so I did this as a work around.

Query2

Field: MyField1ID
Table: Query1

Field: Field2
Table: Query1

Field: MyExpr:
Table: Query1

Field ValueExpr: Val(Query1.MyExpr])
Table: LEAVE BLANK



Note: Query2 last column simple makes MyExpr into a number (or Value) so that the criteria can be a number.

Criteria: It can be whatever number you like Say <=200 – will return the first 200 records

I am guessing you can have the criteria for ValueExpr read off a Form - something like

<=[Forms!form1,TextBox1]

 

[John Big Booty]

Have a look at the attached DB.

It uses an unbound form to allow the user to select the number of records to return and whether they want the top X number or top X percent records.

It then uses the OpenArgs portion of the OpenForm method to pass this information to the form being opened and then to dynamically construct the Control Source of the form accordingly.

Check out the Code behind the button on form FRM_Welcome and then the code in the On Load event of form FRM_Records. The SQL in this event is based on that found in Query1.

 

[Alvis]

Steve & John -

First, thanks for your time.
Both worked .... excellent !

I'm still a learner and I can see long way to go. Thank you Guyz !

- Alvis

 

[Steve C]

Thanks JBB - way WAY better than my effort.

Good luck Alvis - next time somebody asks this question I'm gonna give JBB's answer and pretend that's how clever I am. (not really). Well, a bit really

 

[Alvis]

You both ROCK !
Thanks for the knowledge share .... we'll catch up soon.