Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display x number of records

    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.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Display x number of records

    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......
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display x number of records

    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.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Display x number of records

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display x number of records

    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

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Display x number of records

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

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Display x number of records

    My mistake. Apparently SQL won't accept a parameter for the "TOP n" entry. Apparently, you will have to manually change the value. Sorry.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Display x number of records

    What you could do is build the query on the fly, then do what you need to from there.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display x number of records

    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

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Display x number of records

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display x number of records

    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

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Display x number of records

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  13. #13
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display x number of records

    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

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Display x number of records

    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
    Regards
    John



  15. #15
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display x number of records

    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

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •