Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 Changing query properties in VBA.....

Author  Topic 

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2004-03-25 : 07:25:05
Access 97.........

Can anyone tell me how to change the 'Top Values' property of a query using VBA ? Is it possible ?

I want to be able to change the red bit in the statement below so i can return any 5 records e.g 1-5, 6-10, 11-15, 16-20 etc


SELECT TOP 5
VotingUniverse_TopFirmsVolumeByType.Type,
VotingUniverse_TopFirmsVolumeByType.Volume
FROM
VotingUniverse_TopFirmsVolumeByType
WHERE
(((VotingUniverse_TopFirmsVolumeByType.Volume)
In (SELECT TOP 10 VotingUniverse_TopFirmsVolumeByType.Volume
FROM
VotingUniverse_TopFirmsVolumeByType
ORDER BY Volume DESC)))
ORDER BY
VotingUniverse_TopFirmsVolumeByType.Volume;


i know i could feed in a different number to this SQL statement in code but thought it would be easier to simply change the 'Top Values' property of the relevant query.

There is more SQL to wrap around this bit and i'd rather not have a massive SQL statement in the code.

Essentially i'm not quite sure how to manipulate that property?

Any ideas much appreciated


====
Paul

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-25 : 17:01:43
i believe you need to manipulate the SQL. the TOP is not as much as property as it is something that Access extracts from or inserts into the SQL code itself of the query.

- Jeff
Go to Top of Page

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2004-03-26 : 06:53:43
thanks jeff

i ended up as you say manipulating the SQL and feeding in a variable to change the TOP 10 part to whatever i needed it to be


====
Paul
Go to Top of Page
   

- Advertisement -