Using a form to input for TOP x PERCENT in VBA

[morganaj]

Hello,

I am trying to use this form:

to select a program type and generate a random list to audit. This part is working correctly. But I would like to use a variable either a number or a percent to input as the TOP x PERCENT|NUMBER in my SQL statement from the form.

My code looks like this:

strTableName = "tblAuditSample_" & Format(Date, "ddmmmyyyy")
 
    strSQL = "SELECT TOP 10 PERCENT tblTemp.SHC_No, tblTemp.Project_Name, tblTemp.Program_Type, tblTemp.PMC " & _
                "INTO " & strTableName & " " & _
                "FROM tblTemp " & _
                "ORDER BY tblTemp.RandomNumber;"
 
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
 

When I change the hard coding to either a number or percent this works, but I want other people who are unfamiliar with VBA to be able to generate an audit report too.

Any help would be greatly appreciated!
Thanks.

[zmbd]

morganaj:
1) Your image link is blocked by most company IT policies in the USA and more than likely else where in the world. If the file is small enough, use the advanced editor and then manage attchments to upload the file here.

2) if I understand correctly you want to change this:
strSQL = "SELECT TOP 10 PERCENT tblTemp.SHC_No,
to this:
strSQL = "SELECT TOP X PERCENT tblTemp.SHC_No,

Now because I can't see your form all I can do is provide a basic change. I must assume that there is some sort of command button that executes the VBA Script:

If the form has a text box control named "txtPercent " then you need to validate that the user entry is numeric, positive, and between 1 and 100... that code I leave to you.

Once the validation is done then:
strSQL = "SELECT TOP " & me.txtPercent & " PERCENT tblTemp.SHC_No,

[morganaj]

When I try to change the SELECT statement to include the variable, that you suggested.

 strSQL = "SELECT TOP" & Me.strPercent & "PERCENT tblTemp.SHC_No, tblTemp.Project_Name, tblTemp.Program_Type, tblTemp.PMC " &
                "INTO " & strTableName & " " & _
                "FROM tblTemp " & _
                "ORDER BY tblTemp.RandomNumber;"

It all turns red and gives me a "Compile error: Expected: Expression."

And if I try to run it as is I get a "Compile error: Syntax Error."

Is this because I am running Access 2007, or am I missing an object library...

[morganaj]

Hey zmbd, thanks for the help. I figured it out.

"SELECT TOP" & " " & Me.strPercent & " " & " PERCENT 

Apparently the syntax wanted a literal space to make it run properly.

Thanks for the help!

[zmbd]

Yes, the spacing is very importaint to the SQL engine.
If you will take a very carefull look at the final string in post#2, you may notice this... if one is not expecting to look for the space it is VERY easy to overlook and is one of the more common errors fixed (next the quote marks around strings) in ACC-SQL strings.

BOL W/ Your Project.
-z