TopValues assign in a query

[Guest]

Is there some way to assign topvalues to a query with code. Help screens say
can't do directly, but is there some way indirectly?

 

[Rob Oldfield]

You can always just create a query on the fly with...

dim db as database
set db=currentdb
dim sql="select top x from ...."
dim qdf as querydef
set qdf=db.createquerydef("QueryName",sql)
set qdf=nothing
set db=nothing

....though you'd need a deleteobject in there as well if you want to
overwrite an existing one.

 

[Guest]

This example will insert a new SQL including the top values to existing query.
This function will get a number that stand for how many records, and change
the sql of the query.

Function FunctionName(X as Integer)
Dim db as DAO.database, myqs as querydef, X as Integer
Set db = CurrentDB
Set myqs = db.QueryDefs("QueryName")
myqs.sql = "SELECT Top " & X & " TableName.* From TableName"
End Function

 

[Guest]

Rob I tried this. When I click debug, goes to first line and messages "User
defined type not defined" Third line shows in red. I should have said
earlier that i am using Access 2000 with W2000

 

[Rob Oldfield]

You need a reference to the DAO library. Open up a code window, go to
Tools,.References and scroll down to find and tick Microsoft DAO (latest
version).

 

[Guest]

It works! Thanks very much. If someone else uses my program with their
Access program, I assume that they will also have to activate the DAO from
the Tools.Refereces?

 

[Guest]

Debug still doesn't like the 3rd line. I was able to make the code in the
other reply from Ofer work. For either of these, I assume that the users
have to activate the DAO reference for this to work on their computers?

 

[Guest]

They don't need to, but they might have a problem if the path will be
different, or if the DAO is not intalled in their computer.

 

[Rob Oldfield]

oops. That should be

dim sql as string
sql="...."

 

[Guest]

I spoke too soon. The problem is that the even though the query becomes a
top X type, the code wipes out the sort ascending in the query whenever I
run it . So, naturally, selecting top values can't work properly.

Debug compile also told me to that I had too many "X as integer" 's,. I
got a msg "Duplicate declaration etc." , so I removed the X etc. on the
second line.

Function FunctionName(X as Integer)
Dim db as DAO.database, myqs as querydef, X as Integer
Set db = CurrentDB
Set myqs = db.QueryDefs("QueryName")
myqs.sql = "SELECT Top " & X & " TableName.* From TableName"
End Function

 

[Rob Oldfield]

The sort is disappearing because it's not specified in the SQL that you're
using. Open your initial query in design view and use the View, SQL
command. The text there (with different top values) is what you're going to
need to build. So you need to modify the myqs.sql = line to build that text
(i.e. including the ORDER BY part)

The duplicate definition of X is because it's defined as an argument that
will be fed to the function - in the line

Function FunctionName(X as Integer)

- but also as a variable within the function. You don't need the second one
so just change the second line to just

Dim db as DAO.database, myqs as querydef