Select Top (variable) - Is this possible?

[tho28199]

Hi all,

I have exhausted my search on this question to no avail, so I am turning to the true experts.

Is it possible to use a variable, or alternatively, a form field value, in the SQL Select Top n statement, and if so, what is the appropriate syntax?

I tried using "Select Top varRecords ..." but got a run-time error '3141'.

Any assistance would be greatly appreciated.

 

[Banana]

If you are doing this from VBA, you need to delimit the string from the variable.

Example:

strSQL = "SELECT TOP " & VarRecords & "...

Don't forget the spaces before and after.

 

[tho28199]

Thanks Banana. I had also tried that, but with the same result. Here is the actual code:

strSQL = "SELECT TOP " & varRecord & " tblTemp.[Order Date Year Quarter], " & _

Everything works fine when I use an actual number, so I know the rest of the code is good.

Any other suggestions?

 

[Banana]

Okay, just exactly what is varRecord? A variant? I'd make it an integer...

 

[tho28199]

I have it declared as an integer...

Public varRecord As Integer

I assign the value to varRecord from a bound control on a form, which obtains its value from a query.

varRecord = txtRecords.Value

I then use a command button on that form to call the procedure that contains the problem code.

 

[Banana]

A suggestion.

Run the code with a breakpoint at the line right after the strSQL=....

Use immediate window and enter "?strSQL". Compare that with the SQL with actual number that works. See if there's anything different...

 

[tho28199]

Hi Banana,

I did as you suggested, with the following result:

SELECT TOP 0 tblTemp.[Order Date Year Quarter], etc...

When I saw the 0, I realized I had been testing the code without going through the whole process, so the variable value was not getting set. As soon as I took care of that minor detail, everything worked great.

Thanks for your patience and assistance... it is much appreciated.

 

[Banana]

Yeah, it's almost always the irrelevant details that gets you in the nuts.

Glad to be of help.