Help needed! : SELECT TOP "n", where n = query value

[Chromium]

Hi All, I’m Italian so first of all sorrry for my bad english.

I need to realize a “ SELECT TOP n “ function (function preferred than Sub, cause I can call it in a normal access query), but I don’t know anything about VB. I tried to search on the net, on this forum, and I found some kind of code but anything that works for me (even as a sub).

I have a table with 2296 records (“table”), stored in field as ID, name, surname, etc. What I need to do is sorting “n” field randomly by this table, where “n” is a value provided by another query (es. “[table2]![limit]).

I found here a sub that takes this “table” and make an output called “randomtable”, ordering the “ID” field randomly.

So, now, I need a function to be applied in a new query (es. “randomtable_q1”) that limits the records to the “TOP n” where n = [table2]![limit]

Can someone help me? It’s a real problem…

(I’ve posted this in “macro” and in “ VB code”, please admin remove the post from the forum less appropriate. I’ve done it cause probably this thing can be done via macros and VB.)

 

[Chromium]

no one can help, eh?

thank anyway...

 

[Chromium]

I prepared this code that do a lot of work... but now the problem is to convert the type of "Value" to Integer... it seems that CInt doesn't work..

any suggestion?

Function Pickrandom()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim strSQL As String
Dim strTableName As String
Dim intN As Integer
Dim Value As Double
Dim numerico As String
' 1: Create a new temporary table containing the required fields
strSQL = "SELECT passeggeri_ita.ID " & _
"INTO tblTemp " & _
"FROM passeggeri_ita;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 2: Add a new field to the new table
Set db = CurrentDb()
Set tdf = db.TableDefs("tblTemp")
Set fld = tdf.CreateField("RandomNumber", dbSingle)
tdf.Fields.Append fld
' 3: Place a random number in the new field for each record
Set rst = db.OpenRecordset("tblTemp", dbOpenTable)
rst.MoveFirst
Do
Randomize
rst.Edit
rst![RandomNumber] = Rnd()
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing

' 4: Sort the data by the random number and move the top 25 into a new table

strTableName = "Paxrandom"
numerico = "SELECT richiesta_volo_Q1_elab_maxPAX.NUM_PAX_ECO " & _
"FROM richiesta_volo_Q1_elab_maxPAX " & _
"ORDER BY richiesta_volo_Q1_elab_maxPAX.NUM_PAX_ECO"
Value = CInt(Val(" & numerico & "))
intN = CInt(" & Value & ")
strSQL = "SELECT TOP " & Value & " tblTemp.ID " & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 5: Delete the temporary table
db.TableDefs.Delete ("tblTemp")


End Function

 

[georgedwilkinson]

You cannot take the value of & numerico &.

Before the line that breaks, please put this line of code:

Debug.Print numerico

Single step up to the line with the error and copy and paste the text in the Immediate Window back to this thread. That will help us (and you) determine what is wrong.

 

[Chromium]

You cannot take the value of & numerico &.

Before the line that breaks, please put this line of code:

Debug.Print numerico

Single step up to the line with the error and copy and paste the text in the Immediate Window back to this thread. That will help us (and you) determine what is wrong.

The Error is (I'll try to translate, the line code you indicated don't do anything... i don't understand how it's used):

" Runtime 3141
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
"
I need to recall a value from a query (or from a form) that must be used as "n" in the "SELECT TOP n" query.

Any suggestion is appreciated, it doesn't matter the way to do it, I only need that the work is done

 

[namliam]

strTableName = "Paxrandom"
numerico = "SELECT richiesta_volo_Q1_elab_maxPAX.NUM_PAX_ECO " & _
"FROM richiesta_volo_Q1_elab_maxPAX " & _
"ORDER BY richiesta_volo_Q1_elab_maxPAX.NUM_PAX_ECO"
Value = CInt(Val(" & numerico & "))
intN = CInt(" & Value & ")
strSQL = "SELECT TOP " & Value & " tblTemp.ID " & _

You are using Val on a Select statement??? That is not going to work ... what is it you are trying to do?

Also... instead of this messy bit
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
why not use
Currentdb.execute strSQL
Does the same... but cleaner

 

[Chromium]

what is it you are trying to do?

This:

I need to recall a value from a query (or from a form) that must be used as "n" in the "SELECT TOP n" query.

Any suggestion is appreciated, it doesn't matter the way to do it, I only need that the work is done

(you can expect a lot of error: I never saw anything about VB)

 

[namliam]

Are you looking for something like this?

This will retrieve the NUM_PAX_ECO into the Numerico field...., if that is what you want...
strSQL = "SELECT richiesta_volo_Q1_elab_maxPAX.NUM_PAX_ECO " & _
"FROM richiesta_volo_Q1_elab_maxPAX " & _
"ORDER BY richiesta_volo_Q1_elab_maxPAX.NUM_PAX_ECO"
Set rs = currentdb.openrecordset(strSQL)
numerico = rs!NUM_PAX_ECO
set rs = nothing

 

[Chromium]

Are you looking for something like this?

This will retrieve the NUM_PAX_ECO into the Numerico field...., if that is what you want...

Can I say that I love you?

A pair of modifications and it works like a charm!

Function Pickrandom()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim qdf As QueryDefs
Dim rst As Recordset
Dim strSQL As String
Dim strPRE As String
Dim strTableName As String
Dim intN As Variant
Dim numero As Integer
' 1: Create a new temporary table containing the required fields
strSQL = "SELECT passeggeri_ita.ID " & _
"INTO tblTemp " & _
"FROM passeggeri_ita;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 2: Add a new field to the new table
Set db = CurrentDb()
Set tdf = db.TableDefs("tblTemp")
Set fld = tdf.CreateField("RandomNumber", dbSingle)
tdf.Fields.Append fld
' 3: Place a random number in the new field for each record
Set rst = db.OpenRecordset("tblTemp", dbOpenTable)
rst.MoveFirst
Do
Randomize
rst.Edit
rst![RandomNumber] = Rnd()
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing

' 4: Sort the data by the random number and move the top 25 into a new table

strPRE = "SELECT paxrandom_attuale.NUM_PAX_ECO " & _
"FROM paxrandom_attuale " & _
"ORDER BY paxrandom_attuale.NUM_PAX_ECO"
Set rs = CurrentDb.OpenRecordset(strPRE)
numerico = rs!NUM_PAX_ECO
Set rs = Nothing
strTableName = "Paxrandom"
strSQL = "SELECT TOP " & numerico & " tblTemp.ID,tblTemp.RandomNumber " & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 5: Delete the temporary table
db.TableDefs.Delete ("tblTemp")


End Function

 

[namliam]

Can I say that I love you?

Yes you can, but dont act on it if you are a guy

Even if you are a woman, I doubt my wife would like it....

So unless you want real trouble with one of us (my wife or me), dont act on it

Happy to have helped you

 

[Chromium]

Yes you can, but dont act on it if you are a guy

Even if you are a woman, I doubt my wife would like it....

So unless you want real trouble with one of us (my wife or me), dont act on it

Happy to have helped you

Muaahahaah, no, I'm a guy, but I'm sure that my girlfriend won't like it too