Select Top N, where N is a variable from a form 1

[adita1983]

Hi,

cmb_Procent is a combobox in a form.

Here is my code :

strSQL = "SELECT TOP cint(" & Me.cmb_Procent & " /10) tblTemp.* " & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber;"

The error is : "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Thanks in advance.

Regards,
Adrian

 

[p27br]

your syntax is not correct :

try this

ssql = "SELECT tbltemp.*, CInt(" & Me!cmb_Procent & ")/10 AS Expr1 INTO tbltemp2" & _
" FROM tbltemp" & _
" ORDER BY tbltemp.randomnumber;"

 

[1003]

The calculation of the number of the TOP records must be part of the VBA code, not the SQL statement:

strSQL = "SELECT TOP " & cint(Me.cmb_Procent/10) & " tblTemp.* " & _
             "INTO " & strTableName & " " & _
             "FROM tblTemp " & _
             "ORDER BY tblTemp.RandomNumber;"

 

[PHV]

Another way:
strSQL = "SELECT TOP " & (Me!cmb_Procent \ 10) & " tblTemp.* " & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[p27br]

adita1983

in addition to the code i posted, the errors i found were a missing comma before the tblTemp.* and the fact that the CREATE TABLE won't allow circular reference (create from tbltemp into tbltemp)

 

[adita1983]

Hi,

Thaks guys, it works.The problem was with syntax.

strSQL = "SELECT TOP " & cint(Me.cmb_Procent/10) & " tblTemp.* " & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber;"

is correct.I have one more question.I want someting like that.

strSQL = "SELECT TOP " & cint(Me.cmb_Procent/10*count(*)) & " tblTemp.* " & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber;"

Same problem : The error is : "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Tkss a lot,
Adrian

 

[PHV]

Why not simply using the TOP n PERCENT syntax ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[adita1983]

Hi,

PHV can u be more explicit ?I need Me.cmb_Procent percent from total records of my table.

Tks in advance,
Adrian

 

[PHV]

Something like this ?
strSQL = "SELECT TOP " & Me.cmb_Procent & " PERCENT tblTemp.* " & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244