Enter Parametr Value

[NEWER USER]

I have the following SQL that selects the TOP 10 clients by percentage of
sales. I want to be able to SELECT the TOP 20, TOP 40, TOP 60, etc.. by
entering a value when the query runs. How might I change my SQL? Any help
appreciated.

SELECT TOP 10 tblPct.ip, tblPct.Pct
FROM tblPct
ORDER BY tblPct.Pct DESC;

 

[Tom van Stiphout]

On Fri, 8 Jan 2010 17:52:02 -0800, NEWER USER

The only way to do that is to use dynamic sql.
This will not work:
select top forms!myForm1myControl from myTable
This will not work:
select top [parMyTop] from myTable
(and then trying to set the parameter using the QueryDef object.

But this will work:
dim strSQL as string
strSQL = "select top ..."
Me.RecordSource = strSQL
'etc.

-Tom.
Microsoft Access MVP

 

[John Spencer]

You can either use VBA to build the query string or one of the techniques below.

Select Top N records where N is variable ( a parameter)

One method that can be used is to use a ranking query to assign a number to
the records and then use the ranking to return N records.

'Probably not updatable
SELECT MyTable.*
FROM MyTable INNER JOIN
(
SELECT A.MyField, Count(B.MyField) as TheCount
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
) as Ranking
ON MyTable.MyField = Ranking.MyField
WHERE Ranking.TheCount < [Top How Many Number]

'Probably updatable
SELECT MyTable.*
FROM MyTable
WHERE MyField in
(SELECT A.MyField
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
HAVING Count(B.MyField) < [Top How Many Number])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On Fri, 8 Jan 2010 17:52:02 -0800, NEWER USER

The only way to do that is to use dynamic sql.
This will not work:
select top forms!myForm1myControl from myTable
This will not work:
select top [parMyTop] from myTable
(and then trying to set the parameter using the QueryDef object.

But this will work:
dim strSQL as string
strSQL = "select top ..."
Me.RecordSource = strSQL
'etc.

-Tom.
Microsoft Access MVP

I have the following SQL that selects the TOP 10 clients by percentage of
sales. I want to be able to SELECT the TOP 20, TOP 40, TOP 60, etc.. by
entering a value when the query runs. How might I change my SQL? Any help
appreciated.

SELECT TOP 10 tblPct.ip, tblPct.Pct
FROM tblPct
ORDER BY tblPct.Pct DESC;

 

[NEWER USER]

Still confused a bit. If I type

Private Sub Report_Open(Cancel As Integer)
dim strSQL as string
strSQL = "Select Top 10 tblPct.ip, tblPct.Pct
FROM tblPct
ORDER BY tblPct.Pct DESC;"
Me.RecordSource = strSQL

How will I be prompted to enter Top 10 or Top 35 or Top 75 and have those
records displyed on the report?

On Fri, 8 Jan 2010 17:52:02 -0800, NEWER USER

The only way to do that is to use dynamic sql.
This will not work:
select top forms!myForm1myControl from myTable
This will not work:
select top [parMyTop] from myTable
(and then trying to set the parameter using the QueryDef object.

But this will work:
dim strSQL as string
strSQL = "select top ..."
Me.RecordSource = strSQL
'etc.

-Tom.
Microsoft Access MVP

I have the following SQL that selects the TOP 10 clients by percentage of
sales. I want to be able to SELECT the TOP 20, TOP 40, TOP 60, etc.. by
entering a value when the query runs. How might I change my SQL? Any help
appreciated.

SELECT TOP 10 tblPct.ip, tblPct.Pct
FROM tblPct
ORDER BY tblPct.Pct DESC;

.

 

[John W. Vinson]

Still confused a bit. If I type

Private Sub Report_Open(Cancel As Integer)
dim strSQL as string
strSQL = "Select Top 10 tblPct.ip, tblPct.Pct
FROM tblPct
ORDER BY tblPct.Pct DESC;"
Me.RecordSource = strSQL

How will I be prompted to enter Top 10 or Top 35 or Top 75 and have those
records displyed on the report?

You won't.

If, however, you use

Private Sub Report_Open(Cancel As Integer)
dim strSQL as string
Dim iTop As Integer
iTop = InputBox("Enter top percent:")
strSQL = "Select Top " & iTop & " tblPct.ip, tblPct.Pct
FROM tblPct ORDER BY tblPct.Pct DESC;"
Me.RecordSource = strSQL

you will. You can use a form reference instead of InputBox (it would be
preferable to do so in fact).

 

[Tom van Stiphout]

On Sat, 09 Jan 2010 15:57:40 -0700, John W. Vinson

Indeed a form may be better, or at least be careful that InputBox can
be cancelled, and that value (an empty string) cannot be assigned to
an integer. Slightly better code:
Private Sub Report_Open(Cancel As Integer)
dim strSQL as string
Dim strTop As string
strTop = InputBox("Enter top percent:")
if strTop = "" then
Msgbox "Cannot open this form without a percentage"
Cancel = True 'this will stop the opening of the form
elseif not isnumeric(strTop) then
'TODO: handle this error
else
strSQL = "Select Top " & strTop & " tblPct.ip, tblPct.Pct
FROM tblPct ORDER BY tblPct.Pct DESC;"
Me.RecordSource = strSQL
end if

-Tom.
Microsoft Access MVP

 

[NEWER USER]

I am getting a Syntax error message on the following line. Entire line
highlighted in RED. I cut and pasted into the Event On Open of the report.
Any ideas?

FROM tblPct ORDER BY tblPct.Pct DESC;"

 

[Bob Barrows]

Tom should have written his line like this to avoid the inappropriately
place line break inserted by his or your newsreader:

strSQL = "Select Top " & strTop & " tblPct.ip, tblPct.Pct " & _
" FROM tblPct ORDER BY tblPct.Pct DESC;"

When he wrote the line, it was a _single_ line. The software that
handles news posts split it into two.

 

[John W. Vinson]

I am getting a Syntax error message on the following line. Entire line
highlighted in RED. I cut and pasted into the Event On Open of the report.
Any ideas?

FROM tblPct ORDER BY tblPct.Pct DESC;"

You got bit by the newsgroup message word wrapping to a new line. If you have
a VBA statement which spans two or more lines (as in this case) you need to
use the VBA continuation signal: a blank followed by an underscore _ character
at the end of the line. It's also a bad idea to split a string constant across
two lines. Try

Private Sub Report_Open(Cancel As Integer)
dim strSQL as string
Dim strTop As string
strTop = InputBox("Enter top percent:")
if strTop = "" then
Msgbox "Cannot open this form without a percentage"
Cancel = True 'this will stop the opening of the form
elseif not isnumeric(strTop) then
'TODO: handle this error
else
strSQL = "Select Top " & strTop & " tblPct.ip, tblPct.Pct" _
& " FROM tblPct ORDER BY tblPct.Pct DESC;"
Me.RecordSource = strSQL
end if

 

[NEWER USER]

Thanks to ALL for your help.

You got bit by the newsgroup message word wrapping to a new line. If you have
a VBA statement which spans two or more lines (as in this case) you need to
use the VBA continuation signal: a blank followed by an underscore _ character
at the end of the line. It's also a bad idea to split a string constant across
two lines. Try

Private Sub Report_Open(Cancel As Integer)
dim strSQL as string
Dim strTop As string
strTop = InputBox("Enter top percent:")
if strTop = "" then
Msgbox "Cannot open this form without a percentage"
Cancel = True 'this will stop the opening of the form
elseif not isnumeric(strTop) then
'TODO: handle this error
else
strSQL = "Select Top " & strTop & " tblPct.ip, tblPct.Pct" _
& " FROM tblPct ORDER BY tblPct.Pct DESC;"
Me.RecordSource = strSQL
end if