Top values as a parameter? ? (1 Viewer)

[WineSnob]

Is it possible to have the top value be a parameter. I created a query that give me the top 10 records. Works fine..... Can I make the top value a parameter for the user to input their own value? Example parameter value 50 would be the top 50 or 20% would be the top 20%.

 

[apr pillai]

The Query definition can be redefined through Code. Let the User enter the Parameter (TOP ) value into a Textbox; like 50 or 20%. The User can click a Command Button to run the following Code to redefine the Query:

Private Sub cmdRun_Click()
Dim strSQL1 As String, strSQL2 As String, strTopValue
Dim db As Database, QryDef As QueryDef, sql As String
Dim loc

strSQL1 = "SELECT "
strSQL2 = " Employeess.LastName, Employeess.FirstName, Employeess.EmployeeID "
strSQL2 = strSQL2 & "FROM Employeess ORDER BY Employeess.EmployeeID DESC;"

strTopValue = Me![TopVal]
loc = InStr(1, strTopValue, "%")

If loc > 0 Then
   strTopValue = Left(strTopValue, Len(strTopValue) - 1)
End If

If Val(strTopValue) = 0 Then
   sql = strSQL1 & strSQL2
Else
   sql = strSQL1 & "TOP " & strTopValue & IIf(loc > 0, " PERCENT", "") & strSQL2
End If

Set db = CurrentDb
Set QryDef = db.QueryDefs("Query1")
QryDef.sql = sql

End Sub

 

[WineSnob]

Thanks.... I am working on it to get it to work the way I intend. Very Close...I'll get it

 

[WineSnob]

The query works perfectly. However I am trying to display the results in the detail section of the form. If I close the form AFTER I click the button and reopen the results are correct.
How can get the detail section to refresh and show the new "top values".
Here is my code as modified. THANKS

Private Sub Command56_Click()
Dim strSQL1 As String, strSQL2 As String, strTopValue
Dim db As Database, QryDef As QueryDef, sql As String
Dim loc

strSQL1 = "SELECT "
strSQL2 = " qrySumIncomeByVendor.VENDOR, Sum(qrySumIncomeByVendor.[Actual Income]) AS [SumOfActual Income] "
strSQL2 = strSQL2 & "FROM qrySumIncomeByVendor GROUP BY qrySumIncomeByVendor.VENDOR ORDER BY Sum(qrySumIncomeByVendor.[Actual Income]) DESC;"
strTopValue = Me![TopVal]
loc = InStr(1, strTopValue, "%")
If loc > 0 Then
strTopValue = Left(strTopValue, Len(strTopValue) - 1)
End If
If Val(strTopValue) = 0 Then
sql = strSQL1 & strSQL2
Else
sql = strSQL1 & "TOP " & strTopValue & IIf(loc > 0, " PERCENT", "") & strSQL2
End If
Set db = CurrentDb
Set QryDef = db.QueryDefs("qryTOP10byIncome")
QryDef.sql = sql
Me.Requery
Me.Refresh
End Sub

 

[nortonm]

The Query definition can be redefined through Code. Let the User enter the Parameter (TOP ) value into a Textbox; like 50 or 20%. The User can click a Command Button to run the following Code to redefine the Query:

Private Sub cmdRun_Click()
Dim strSQL1 As String, strSQL2 As String, strTopValue
Dim db As Database, QryDef As QueryDef, sql As String
Dim loc

strSQL1 = "SELECT "
strSQL2 = " Employeess.LastName, Employeess.FirstName, Employeess.EmployeeID "
strSQL2 = strSQL2 & "FROM Employeess ORDER BY Employeess.EmployeeID DESC;"

strTopValue = Me![TopVal]
loc = InStr(1, strTopValue, "%")

If loc > 0 Then
   strTopValue = Left(strTopValue, Len(strTopValue) - 1)
End If

If Val(strTopValue) = 0 Then
   sql = strSQL1 & strSQL2
Else
   sql = strSQL1 & "TOP " & strTopValue & IIf(loc > 0, " PERCENT", "") & strSQL2
End If

Set db = CurrentDb
Set QryDef = db.QueryDefs("Query1")
QryDef.sql = sql

End Sub

Superb code, many thanks.