How to return "x" records in a query

[racefan91]

I'm working on a database at my work and am having trouble finding any info on how to set the number of rows i want to display in a query. I can do it through the top values parameter in the design view properties but was wondering if there is an easier way to go about setting that value. Tried doing something like [TopValues] in the criteria but that doesn't do anything. The following sql is for the query I am trying to use with setting the Top Values parameter to 12.



SELECT TOP 12 tblProductionSchedule.SerialNumber
FROM tblMain INNER JOIN tblProductionSchedule ON tblMain.[AB#] = tblProductionSchedule.SerialNumber
GROUP BY tblProductionSchedule.SerialNumber, tblProductionSchedule.[Item#3], tblMain.DateBuilt, tblMain.[Cancel Date]
HAVING (((tblProductionSchedule.[Item#3]) Is Not Null) AND ((tblMain.DateBuilt) Is Null) AND ((tblMain.[Cancel Date]) Is Null))
ORDER BY tblProductionSchedule.[Item#3];

[CJ_London]

can't be passed as a criteria as such - you need to use vba, either to replace the sql in a querydef or to apply to a form or report recordsource

note that the use of spaces and non alpha numeric characters such as # can cause weird errors, better to use neither - # for example is used as a date delimiter in sql and in a form you if you have code for a control called say Cancel Date, the relevant sub will be called Sub Cancel_Date whatever - it replaces spaces with an underline


in a form you would use something like

dim sqlstr as string

sqlstr="SELECT TOP " & txtNumRows & " tblProductionSchedule.SerialNumber.....

querydefs("queryname").sql=sqlstr

or you might use

me.recordsource=sqlstr

[Gicu]

You can use the .SQL property of the QueryDef object based on your query to edit the number that comes after TOP:
In your VBA procedure you would then use the Replace() function to update the .SQL property to the new number:

Dim qdf as DAO.QueryDef,sSQL as String
Dim sExistingTop as String


Set qdf=CurrentDb.QueryDefs("qryYourQuery")
sSQL=qdf.SQL
sExistingTop= Trim(Replace(Left(sSQL,InStr(sSQL,"tblProductionSchedule.SerialNumber")-1),"SELECT ","")) 'TOP 12 in your exmple
sSQL=Replace(sSQL,sExistingTop,"TOP " & Me.txtNewTopNumber) 'uses a textbox one the form to pass the new number, you can use another method such as an input box
qdf.SQL=sSQL 'update the query
Set qdf=Nothing

Cheers,

[racefan91]

You can use the .SQL property of the QueryDef object based on your query to edit the number that comes after TOP:
In your VBA procedure you would then use the Replace() function to update the .SQL property to the new number:

Dim qdf as DAO.QueryDef,sSQL as String
Dim sExistingTop as String


Set qdf=CurrentDb.QueryDefs("qryYourQuery")
sSQL=qdf.SQL
sExistingTop= Trim(Replace(Left(sSQL,InStr(sSQL,"tblProductionSchedule.SerialNumber")-1),"SELECT ","")) 'TOP 12 in your exmple
sSQL=Replace(sSQL,sExistingTop,"TOP " & Me.txtNewTopNumber) 'uses a textbox one the form to pass the new number, you can use another method such as an input box
qdf.SQL=sSQL 'update the query
Set qdf=Nothing

Cheers,

Sorry for not understanding, my knowledge of access is very limited. How do I use this code? I make a textbox and then attach the vba to that textbox through one of the events?

[Gicu]

Almost, but preferably you create a new simple (popup) form with a text box for the new number and a command button to run the code. You can obviously add those two controls to an existing form if you have one (such as a switchboard or a "reporting" form where you set some other options\parameters).

Make sure you customize the code I've given you by updating qryYourQuery with the actual name you use. You might also want to add a message box at the beginning of the code to prompt the user to confirm the change that is about to happen.

Cheers,