select top 100 in query variable text box

[shutzy]

i have got a query that returns the top 100 results. i would like to link this to my report form where i have a text box that you can enter a number and the query returns the first of that ammount rather than going into the query everytime to adjust the results.

the sql of the query is as follows

SELECT TOP 100 tblClientDetails.FirstName, tblClientDetails.Surname, Sum(tblOrdersItems.Cost) AS SumOfCost
FROM (tblClientDetails INNER JOIN tblOrders ON tblClientDetails.ClientDetailsID = tblOrders.ClientDetailsID) INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID
WHERE (((tblOrders.OrderDate)>DateAdd('yyyy',-1,Date())))
GROUP BY tblClientDetails.FirstName, tblClientDetails.Surname
ORDER BY Sum(tblOrdersItems.Cost) DESC;

thanks

 

[pr2-eugin]

Not sure if you can do it on Query based.. but might be possible with VBA..
This might not be the greatest option.. So all you need to do run the Query dynamically..
First Create a Form with a Text box and a Button.. So use the text field to populate the number of records you wish to have.. and the button to run the query.. Then on the onClick of the button run the Query.. something like..

Private Sub [COLOR=Blue][B]QueryCommand[/B][/COLOR]_Click()
    Dim qdfNew As DAO.QueryDef
    Dim sqltext As String
    
    sqltext = "SELECT TOP " & Me.[B][COLOR=Blue]yourTextFieldName[/COLOR][/B] & " tblClientDetails.FirstName, tblClientDetails.Surname, Sum(tblOrdersItems.Cost) AS SumOfCost " & _
"FROM (tblClientDetails INNER JOIN tblOrders ON tblClientDetails.ClientDetailsID = tblOrders.ClientDetailsID) INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID " & _
"WHERE (((tblOrders.OrderDate)>DateAdd('yyyy',-1,Date()))) " & _
"GROUP BY tblClientDetails.FirstName, tblClientDetails.Surname " & _
"ORDER BY Sum(tblOrdersItems.Cost) DESC;"
    On Error Resume Next
    With CurrentDb
        .QueryDefs.Delete ("My_Query")
        Set qdfNew = .CreateQueryDef("My_Query", sqltext)
        .Close
    End With
    DoCmd.OpenQuery ("My_Query")
End Sub

Blue bits need to change..

 

[shutzy]

thanks for the reply. at least i know it is possible. i would prefer to do this through a query. is it possible to do this in the criteria. i know that you can select the 'First' record or 'Last' in the toals section so im wondering if it is possible to select the 'First Five' for example. if not it will have to be the top 100 as default and then if it is the top 5 they want they can just look at the first five.

thanks

 

[pr2-eugin]

Did you try the above mentioned method?? It creates a Query, with the criteria i.e. the number of records that you desire.. and then opens it..See the attachment...

 

[shutzy]

i know i might be sounding dificult but im not trying to be. its just that i have a certain way of structuring my database. i have not really used vba unless absolutly neccessary. so i do appreciate the working example and this will be used if another option does not come through. please dont take it the wrong way.

i would still only class myself as a novice so i try to keep things simple and where i know i can adapt things when i need to. thats why i generally stay with the design query etc.

 

[pr2-eugin]

Its alright.. I understand.. As the saying goes "you can only lead the horse to the water you cannot make it drink".. I am not completely versed in access as well.. just my opinion on doing this simpler.. I will watch this thread closely as well; if someone has a better solution I will learn a new thing today... Good luck..