By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,948 Members | 1,929 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,948 IT Pros & Developers. It's quick & easy.

Using a form to input for TOP x PERCENT in VBA

P: 12
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:
Expand|Select|Wrap|Line Numbers
  1. strTableName = "tblAuditSample_" & Format(Date, "ddmmmyyyy")
  2.  
  3.     strSQL = "SELECT TOP 10 PERCENT tblTemp.SHC_No, tblTemp.Project_Name, tblTemp.Program_Type, tblTemp.PMC " & _
  4.                 "INTO " & strTableName & " " & _
  5.                 "FROM tblTemp " & _
  6.                 "ORDER BY tblTemp.RandomNumber;"
  7.  
  8.     DoCmd.SetWarnings False
  9.     DoCmd.RunSQL strSQL
  10.     DoCmd.SetWarnings True
  11.  
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.
Dec 10 '13 #1

✓ answered by 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,

Share this Question
Share on Google+
4 Replies


zmbd
Expert Mod 5K+
P: 5,397
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,
Dec 10 '13 #2

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

Expand|Select|Wrap|Line Numbers
  1.  strSQL = "SELECT TOP" & Me.strPercent & "PERCENT tblTemp.SHC_No, tblTemp.Project_Name, tblTemp.Program_Type, tblTemp.PMC " &
  2.                 "INTO " & strTableName & " " & _
  3.                 "FROM tblTemp " & _
  4.                 "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...
Dec 10 '13 #3

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

Expand|Select|Wrap|Line Numbers
  1. "SELECT TOP" & " " & Me.strPercent & " " & " PERCENT 
Apparently the syntax wanted a literal space to make it run properly.

Thanks for the help!
Dec 10 '13 #4

zmbd
Expert Mod 5K+
P: 5,397
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
Dec 10 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.