Display x number of records

Topic

New Reply

How can I create a query / report that would allow me to display n number of records – where it would ask for the number I want to see? Sure hope someone can help. This is the first time I have tried a forum. This looks very good and informative to me. Thanks in advance.

Reply | Quote

Replies

The basic format of such a query is:
SELECT TOP n Field1, …. FROM table1

You can have Access ask you for the number by making it a parameter query, like this:
SELECT TOP [How many do you want?] Field1, ….etc……

Reply | Quote

Where do I enter the syntax ? I tried entering it in the SQL screen then in the criteria row and it just give me an error. Can you give me any other suggestions. Thank you for your time.

Reply | Quote

When you say you tried entering it into the SQL screen, did you mean that you selected a NEW query?
What did you actually enter, post what you entered.
Pat

Reply | Quote

I didn’t create a new query – I have a query set up with 3 tables. I have an ID field for names and I want to be able to select x number of names at random. Hope I am explaining this ok.. So I typed into the SQL screen in the query – where it starts
SELECT TOP [HOW MANY?], ATA.Surname, ATA.Title, ATA.Prename, ATA.EmailAddress, ATA.HomeEmailAddress, ATA_Committee.Lcomm, Committee.TabComm, ATA.ID AS [Count]
FROM (ATA INNER JOIN ATA_Committee ON ATA.ID = ATA_Committee.ID) INNER JOIN Committee ON ATA_Committee.Lcomm = Committee.Code;

Then I tried typing in the same thing in the criteria row.
Thanks

Reply | Quote

Your SQL looks fine, except you don’t need the comma after [HOW MANY?] .
What does the SQL do when you execute it?
Pat

Reply | Quote

The select statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect. Is there any other way I can do this so that it would ask for an x number of records. Thanks. Robin

Reply | Quote

Setup a form that has a text field for the Top parameter.

Then when you need to run the report via a button on the form, build the query on the fly and write it back as a query.

Base your report on this query.

Pat

Reply | Quote

Pat thanks I will give that a try. I create a form with an unbound text box? How do I create a query on the fly . As you can tell I’m new to Access. Thanks for your patience and time.
Robin

Reply | Quote

Start by:
1. Creating a query (named qry TopN) using what you already have and put the Top number as anything you like because we are going to be creating this query eveytime anyway.
2. Create the report based upon this query.
3.. Create the form with the unbound text box (called textTopN) and a button that prints the report (make it a preview while you are testing so you don’t waste too much paper).
In the OnClick event of the button we need to put the following code just prior to the DoCmd.OpenReport command.
Dim db as database, qdf as QueryDef, strSQL as string
Set db = currentDB
strSQL = “SELECT TOP ” & Me!textTopN & “field1, etc FROM tablename ”
strSQL = strSQL & ” WHERE ………”
db.QueryDefs.Delete “qry TopN”
Set qdf = db.createquerydef(“qry TopN”, strSQL)

DoCmd.OpenReport …….

Set db = nothing
Set qdf = nothing

Hope this helps, if you have any problems just holler.
Pat

Reply | Quote

Hi Again,
This is what I typed in at the On Click code. It won’t run at all.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim db As database, qdf As QueryDef, strSQL As String
Set db = CurrentDb
strSQL = “SELECT TOP ” & Me!textTopN & “ATA.Surname, ATA.Title, ATA.Prename, ATA.EmailAddress, ATA.HomeEmailAddress, ATA_Committee.Lcomm, Committee.TabComm,ATA.EmailAddress, ATA.HomeEmailAddress FROM ATA”
strSQL = strSQL & “Where…..”
db.QueryDefs.Delete “qry Query2”
Set qdf = db.createquerydef(“Query2”, strSQL)

Dim stDocName As String

stDocName = “Query2”
DoCmd.OpenReport stDocName, acPreview

Set db = Nothing
Set qdf = Nothing

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

Reply | Quote

You need to be clear about the name for your query. You use different names on different lines.

db.QueryDefs.Delete “qry Query2”
Set qdf = db.createquerydef(“Query2”, strSQL)
Either call it “qyr Query2” or “Query2”
Before the line db.QueryDefs.Delete “qry Query2”, put in a new line
msgbox(strSQL) so that you can look at the sql string you have built.
This line strSQL = “SELECT TOP ” & Me!textTopN & “ATA.Surname, etc” needs a space before ATA.surname
strSQL = “SELECT TOP ” & Me!textTopN & ” ATA.Surname, etc

Reply | Quote

Pat – I did catch that name error in qry Query2 and added the msg box you suggested – but now I get a “user defined type not identified error and it highlights the Dim db AS database. Hope I’m not taking too much of your Friday night time up.
Robin

Reply | Quote

It’s not Friday night here, it’s 12:16 Saturday afternoon.
What version of Access are you running?
This looks like a conflict with references/ADO !!
Pat

Reply | Quote

Where do you live???? Oh Oh – now what???? I have Access 2002

Reply | Quote

I live in Melbourne, Australia.
Where do you live ?
Pat

Reply | Quote

I live in just outside Edmonton, Alberta, Canada (St. Albert). What is the weather like there. Here it is cold (9 Celcius) and very windy today. Isn’t it funny – here we are communicating from other sides of the world. You are very clever with Access. You must have lots of training (background) with the program. Thanks again for all your help. I was so excited when it worked.
Robin

Reply | Quote

Hi Robin,
It’s always good to know where people come from and hence what time it is where they are.
It’s about 18 Celsius currently, I’m about to go take my dogs training.
It’s funny I have been programming for about 38 years and I still get a kick when something I have written works, it’s a great feeling.

I have been into Access for about 5 years now, but there are a lot of people who know a lot more than I do.
Pat

Reply | Quote

What kind of dogs do you have. I have an American Eskimo Miniture – her name is Samatha but I call her Sammy. Your day is just starting and ours is just over with – it is now 10:30 p.m. Thanks again take care.
Robin

Reply | Quote

I have German Shorthaired Pointers which I enter in Retrieving Trials, it’s great fun, the dogs love it too.
It’s 2:30pm here.
See you next time.
Pat

Reply | Quote

The code Pat posted is DAO code (see, Pat, you have used DAO ), which means you need to have a DAO 3.6 reference set in order for it to work. Access 2000 and 2002 default to an ADO reference, not DAO. If you have both an ActiveX Data Objects (ADO) reference and a Microsoft DAO reference set, you’ll need to change the code like this:

Dim db As DAO.database, qdf As DAO.QueryDef, strSQL As String

Reply | Quote

Thanks Charlotte, I have just been into Tool/References and I realised that I needed to check the Microsoft DAO 3.5 Object Library or Microsoft DAO 3.6 Object Library as you have pointed out. I was just about to tell Robin to tick the 3.5 as I have done in Access 97 but I was not sure.
Do you have to change the code to differentiate between ADO and DAO?
Pat

Reply | Quote

For Access 2000 or 2002, the DAO reference is 3.6.

ADO and DAO are different object models and you can’t mix them in a single routine, although you can use them in the same application. Unfortunately, both models contain objects with the same name, like Recordset, Property, Field, Parameter, etc. So if you don’t declare those object variables specifically as ADODB.Recordset or DAO.Property, you’ll get an error because the methods and properties of those objects are not the same in DAO and ADO. The order of the references in the list (ADO above DAO or vice versa) affects which model Access assumes when it comes across and ambiguous declaration like “Dim rst As Recordset”. If ADO is listed above DAO in references, Access will assume that means an ADODB.Recordset and will choke on methods that don’t belong in the ADO recordset object. You’ll also run into problems with “Dim db As Database” because the ADO model doesn’t have a database object. It also doesn’t have QueryDef or TableDef objects so Access will object to those if you don’t specific the object model in the declaration.

The safest way to handle it is always to declare your object variables by specifying the object model: Dim rst As DAO.Recordset or Dim rst As ADODB.Recordset, etc. ADO is an entirely different object model which is not specifically Access-aware the way DAO is. Microsoft has a site for ADO at http://microsoft.com/data/ado/%5B/url%5D and you might want to read the MSKB article Q225048 “INFO: Issues Migrating from DAO/Jet to ADO/Jet”.

Reply | Quote

Thanks Charlotte. I had heard reference to ADO and DAO but didn’t know what they meant. I also went into help and read up on References. I went into Tools, References and there is a whole list of them – some checked and most not. I did add a couple that sounded like I might need them. There is sooooo much to learn. Thanks Again. Robin

Reply | Quote

Be careful of setting references just because they look likely. I would advise you to not set references unless you know you need them. There are some you can’t avoid because Access won’t let you remove them, but the rest are only needed when your code actually uses calls into that library.

Reply | Quote

What !! You don’t want to talk about dogs ??

Thanks for that explanation re DAO and ADO, that is very helpful. I had not been explicitly coding in DAO only indirectly, thru the use of the library.
Thanks again, you are most helpful.

Pat

Reply | Quote

I like dogs, I just don’t want to live with them. I had an 85lb female doberman–not fat, just BIG–for about 11 years way back when, but I pefer cats as housemates.

Reply | Quote

As someone just told me,

Dogs have owners. Cats have staff.

Hit the nail on the head for me.

Yep, I’m a dog lover. With two cats

Reply | Quote

Oh, yeah! I’ve heard that one before. Obviously someone familiar with cats wrote that.

Reply | Quote

John is correct with the points he has made.

Before you go any further, get your object names right.
Use ‘qry Query2’ as your query, use ‘prt Query2’ as your report.

Also take out the line ‘Where…’, I put that there to show you where the WHERE clause goes as well as showing how to add it to the string strSQL.

The code that you have there will work in Access97 or Access2000, I just don’t know about Access 2002, but I think you need to use DAO commands, maybe Charlotte or someone will chime in here and tell you more about this. I haven’t used DAO (or ADO for that matter) yet either.

Pat

Reply | Quote

What?!! You wrote DAO code without knowing it??

Reply | Quote

The mind just boggles, doesn’t it !!
Pat

Reply | Quote

Oh Pat, Charlotte,John and everyone I love you all dearly – it worked after all that. Thank you so much.

Reply | Quote

Good for you !! I’m glad you got it !!

Pat

Reply | Quote

My mistake. Apparently SQL won’t accept a parameter for the “TOP n” entry. Apparently, you will have to manually change the value. Sorry.

Reply | Quote

What you could do is build the query on the fly, then do what you need to from there.
Pat

Reply | Quote