variable in query

[susan]

Hi,

My query looks like this:

## ---
SELECT TOP 21 QVarHitsSel.hit, QVarHitsSel.titelschoon, QVarHitsSel.lokkaal,
QVarHitsSel.lokatie, QVarHitsSel.titel, QVarHitsSel.volg,
QVarHitsSel.mijnchceck, VarTrackSpecs.toegevoegd
FROM QVarHitsSel INNER JOIN VarTrackSpecs ON
QVarHitsSel.titel=VarTrackSpecs.titel
ORDER BY VarTrackSpecs.toegevoegd DESC;
## ---

Now I want to make it flexible by changing "21" into "MyNumber".
MyNumber is a public variable.
MyMu,ber os set before the query will be opened.


How must I change my query to make this work?

Thanks,

Sysan

 

[Bob Barrows]

Hi,

My query looks like this:

## ---
SELECT TOP 21 QVarHitsSel.hit, QVarHitsSel.titelschoon,
QVarHitsSel.lokkaal, QVarHitsSel.lokatie, QVarHitsSel.titel,
QVarHitsSel.volg, QVarHitsSel.mijnchceck, VarTrackSpecs.toegevoegd
FROM QVarHitsSel INNER JOIN VarTrackSpecs ON
QVarHitsSel.titel=VarTrackSpecs.titel
ORDER BY VarTrackSpecs.toegevoegd DESC;
## ---

Now I want to make it flexible by changing "21" into "MyNumber".
MyNumber is a public variable.
MyMu,ber os set before the query will be opened.


How must I change my query to make this work?

Thanks,

Sysan

You can't without using dynamic sql.
strsql="SELECT TOP "& MyNumber & " ... "

 

[John Spencer]

You can do this if you use a ranking query.

'Probably updateable query. If you don't need to update the results then you
could use a more efficient method of establishing the ranking.
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])

You probably have to change the sub-query to use > rather than <. THat should
be enough to get you started.

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