Solved Selext top X

[Eljefegeneo]

I want to select the top X in a query. I know how to do it using return X from the design tab or Select top X in the SQL statement, but is there any work around for setting a parameter in a form to do this. All the info I found so far says no, but perhaps someone has found some way to do this.

 

[theDBguy]

Hi. Unfortunately, the only way is to dynamically adjust the SQL using code.

 

[Eljefegeneo]

Hi. Unfortunately, the only way is to dynamically adjust the SQL using code.

 

[Eljefegeneo]

I am trying that but can't make it work. so far I have tried

Dim sSql As String
Dim sDrip As Long
sDrip = Forms!frmCampaignsToSend!QuantityToSend
sSql = "SELECT TOP sDrip tblMain.ClientID, tblMain.FirstName, tblMain.Organization," 
etc.

 DoCmd.RunSQL sSql

Still get the can't use a parameter error.

 

[theDBguy]

I am trying that but can't make it work. so far I have tried

Dim sSql As String
Dim sDrip As Long
sDrip = Forms!frmCampaignsToSend!QuantityToSend
sSql = "SELECT TOP sDrip tblMain.ClientID, tblMain.FirstName, tblMain.Organization,"
etc.

DoCmd.RunSQL sSql

Still get the can't use a parameter error.

Hi. Not sure you're doing it correctly. Take a look at the QueryDef object.

 

[Eljefegeneo]

I've looked and looked but cannot seem to figure out what anyone is saying regarding QueryDef object. So perhaps if I explain what I am trying to do there may be a simpler solution.

I have a list of perhaps 1000 names. I want to select the first 100 or so and send them an email which I will call SalesLetter1. Then I want to select the next 100 and send them SalesLetter1 while I send the first 100 SalesLetter2. And so on. I created a table for the various sales letters, 1 through 10.

As things progress I may want to change the number of names to be selected at one time due to changes in the number of entries in the list. This is what I need. Don't want the user to go and modify the SQL statement.

I've got a second part to all this, but it predicates on solving this first.

 

[theDBguy]

Try this as an example.

Dim lngTop As Long
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

lngTop = 10
strSQL = "SELECT Top " & lngTop & " * FROM TableName. 

Set db = CurretDb()
Set qdf = db.QueryDefs("QueryName")
qdf.SQL = strSQL
Set qdf = Nothing 
Set db = Nothing

 

[Isaac]

@Eljefegeneo
Pardon me for jumping in, I just wanted to make a comment directed specifically at your original code.

Try changing this line:

sSql = "SELECT TOP sDrip tblMain.ClientID, tblMain.FirstName, tblMain.Organization,"

to this:

sSql = "SELECT TOP " & sDrip & " tblMain.ClientID, tblMain.FirstName, tblMain.Organization from tblMain"

I didn't pay a lot of attention to the overall SQl statement, just addressing how you put the variable in there.

 

[Eljefegeneo]

Thanks. I was preoccupied with a related item and your answer was right on. Just needed to use query defs. Learned a lot too!

other thread: https://www.access-programmers.co.uk/forums/threads/sql-statement.313956/#post-1721728

 

[theDBguy]

Thanks. I was preoccupied with a related item and your answer was right on. Just needed to use query defs. Learned a lot too!

other thread: https://www.access-programmers.co.uk/forums/threads/sql-statement.313956/#post-1721728

Hi. Glad we could assist. @Isaac and I were happy to help. Good luck with your project.

 

[Isaac]

Thanks. I was preoccupied with a related item and your answer was right on. Just needed to use query defs. Learned a lot too!

other thread: https://www.access-programmers.co.uk/forums/threads/sql-statement.313956/#post-1721728

Glad to hear you got it working! The querydef method is indeed invaluable. One saved query object as a 'bucket' to use and you are off to an infinite # of uses.