Using TempVar variable in SELECT TOP N

[prof.fred]

I have read a number of the threads on using a variable in Select statements using VBA code, however I gather my limited knowledge of VBA within a module is catching me off guard in making it work. The report the query is attached to requires a bit of sorting and would be much easier to modify if a variable rather than a parameter, in this case 120. The following is the 'query' sql code attached to the form, and it works fine as long as I only use a number. Please help me modify it to allow a variable for the '120', the variable is a temp variable - [TempVars]![TempNoQuestions]. The program runs in ACCESS 2007 & 2010 fine otherwise. Any assistance to resolve this would be grandly appreciated and eliminate additional months of headache pondering this issue.

SELECT TOP 120 TestBank.ID, TestBank.RndNo, TestBank.QuesNo, TestBank.Question, TestBank.AnswerA, TestBank.AnswerB, TestBank.AnswerC, TestBank.AnswerD, TestBank.AnswerE, TestBank.AnswerF, TestBank.CorrectAnswer, TestBank.Comment, TestBank.QuestionType, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.[NCCPAKnowledge&Skills], TestBank.E_Label, TestBank.Exam1, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.Status, "A. " & [AnswerA] AS Expr1, "B. " & [AnswerB] AS Expr2, "C. " & [AnswerC] AS Expr3, "D. " & [AnswerD] AS Expr4, [E_Label] & [AnswerE] AS Expr5, "F. " & [AnswerF] AS Expr6, TestBank.Image, TestBank.Author, TestBank.Grading, TestBank.DateEntered, TestBank.BlueprintOrganSystem, TestBank.BlueprintOrganSubCat, TestBank.BlueprintTopic, TestBank.Syllabus, TestBank.[Learning Objective], TestBank.Source, TestBank.BlueprintTopic2, TestBank.BlueprintTopic3, TestBank.BlueprintTopic4, TestBank.[NCCPA Knowledge&Skills2], TestBank.[NCCPA Knowledge&Skills3], TestBank.Pvalue
FROM TestBank INNER JOIN BloomsTaxonomy ON TestBank.BloomsTax = BloomsTaxonomy.ID
WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And [TempVars]![TempPointBiserialHigh]) AND ((TestBank.BloomsTax) Is Null Or (TestBank.BloomsTax) Between [TempVars]![TempBloomsTaxLow] And [TempVars]![TempBloomsTaxHigh]) AND ((TestBank.DateRevised) Is Null Or (TestBank.DateRevised) Between [TempVars]![TempDateRange1] And [TempVars]![TempDateRange2]) AND ((TestBank.[NCCPAKnowledge&Skills]) Is Null Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas1] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas2] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas3] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas4] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas5] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas6] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas7]) AND ((TestBank.Exam1)=[TempVars]![TempExam]) AND ((TestBank.Status)=[TempVars]![TempStatus])) OR (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And [TempVars]![TempPointBiserialHigh]) AND ((TestBank.BloomsTax) Is Null Or (TestBank.BloomsTax) Between [TempVars]![TempBloomsTaxLow] And [TempVars]![TempBloomsTaxHigh]) AND ((TestBank.DateRevised) Is Null Or (TestBank.DateRevised) Between [TempVars]![TempDateRange1] And [TempVars]![TempDateRange2]) AND ((TestBank.[NCCPAKnowledge&Skills]) Is Null Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas1] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas2] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas3] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas4] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas5] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas6] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas7]) AND ((TestBank.Exam2)=[TempVars]![TempExam]) AND ((TestBank.Status)=[TempVars]![TempStatus])) OR (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And [TempVars]![TempPointBiserialHigh]) AND ((TestBank.BloomsTax) Is Null Or (TestBank.BloomsTax) Between [TempVars]![TempBloomsTaxLow] And [TempVars]![TempBloomsTaxHigh]) AND ((TestBank.DateRevised) Is Null Or (TestBank.DateRevised) Between [TempVars]![TempDateRange1] And [TempVars]![TempDateRange2]) AND ((TestBank.[NCCPAKnowledge&Skills]) Is Null Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas1] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas2] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas3] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas4] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas5] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas6] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas7]) AND ((TestBank.Exam3)=[TempVars]![TempExam]) AND ((TestBank.Status)=[TempVars]![TempStatus])) OR (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And [TempVars]![TempPointBiserialHigh]) AND ((TestBank.BloomsTax) Is Null Or (TestBank.BloomsTax) Between [TempVars]![TempBloomsTaxLow] And [TempVars]![TempBloomsTaxHigh]) AND ((TestBank.DateRevised) Is Null Or (TestBank.DateRevised) Between [TempVars]![TempDateRange1] And [TempVars]![TempDateRange2]) AND ((TestBank.[NCCPAKnowledge&Skills]) Is Null Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas1] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas2] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas3] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas4] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas5] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas6] Or (TestBank.[NCCPAKnowledge&Skills])=[TempVars]![TempKnowledge&SkillAreas7]) AND ((TestBank.Exam4)=[TempVars]![TempExam]) AND ((TestBank.Status)=[TempVars]![TempStatus]))
ORDER BY TestBank.RndNo;

 

[pr2-eugin]

This is quiet a loooooooooooooong query. Check out QueryDefs.. Using QueryDef, you can edit the SQL..

 

[Cronk]

Let's say your query is currently saved as qryMyQuery. Remove the "TOP 120" part from the query so it returns all records.

In your report in the OnOpen event, add the following code

Dim n as integer

n = 120    'I don't know where you are getting the value from, so hard coded here
me.recordsource = "SELECT TOP " & cstr(n) & " * FROM qryMyQuery"

 

[prof.fred]

The temp variable is defined as [TempVars]![TempNoQuestions] which is what I am attempting to substitute for the parameter '120' in this example, so it is already defined (and errors if redefined in the OnOpen event, that is dim [TempVars]![TempNoQuestions] as integer).

Per your recommendation, I put in the OnOpen event:

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT TOP " & ([TempVars]![TempNoQuestions]) & " * FROM N_Key_NoFilter_Query_TestBank_AnswerABCDEF"

End Sub

(Yes the name of the query is indeed "N_Key_NoFilter_Query_TestBank_AnswerABCDEF" to distinguish it from others with similar form.) And also tried it with the cstr([TempVars]![TempNoQuestions]) but no luck. The error message given is:"The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." This is the same error I got when I attempted to put the variable into the query statement directly, I assumed since a parameter such as a number (120, ALL, etc) are expected.

Again, any ideas to move this forward will be greatly appreciated. The decreased amount of forms, queries and reports by using a variable instead of a different number in each would make this chore fantastically easier. Sorry, my knowledge of VBA is not up to snuff.

 

[Cronk]

Firstly, does your query N_Key_NoFilter_Query_TestBank_AnswerABCDEF return any data?

Next, what is the value of [TempVars]![TempNoQuestions]? Insert the line

Debug.print  "SELECT TOP " & ([TempVars]![TempNoQuestions]) & " * FROM N_Key_NoFilter_Query_TestBank_AnswerABCDEF"

as the first line in your sub, and see what is the output in the immediate window.

 

[prof.fred]

The query works fine and will give back the correct records based on a sort of the multiple variables in the query that are chosen in another form. The number of records to pull has only worked with a parameter (e.g. number or ALL) so far.

Variable 'NoQuestions' is assigned as a temp variable to use in other reports and queries as [TempVars]![TempNoQuestions], the temp variable is assigned in an OnUpdate event in a connected form. So the variable changes with each form change. It appears to transfer its value correctly between the forms-queries-reports, so that does not appear to be the issue.

I put the debug line in the sub routine following the 'private sub...' line, it returns the same error as before w/o any additional error statement: "The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.".

Again, any help is greatly appreciated.

 

[Cronk]

...and what was the message in the Immediate window? If this is not showing, press Ctrl +G in the VBA editor.

 

[prof.fred]

I may have reversed the name of a query with a report in the OnOpen statement, with that change it appears to be working correctly where if I change the variable NoQuestions in the form, then it updates to a temp variable [TempVars]![TempNoQuestions], then I open the report with your Me.RecordSource... with the correct query name, I get it to change to the correct number of records listed.

I do believe that is a thank you very much. I will play with it some more to be sure it does it in all the appropriate places and get back if I am still not so successful.

Thank you ever so muchly.

 

[prof.fred]

The above sub report continues work well for pulling a specific number of records with a temp variable. But now I need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. ACCESS does not have the OnOpen function in the design view of a query like in the report, but it does allow a SELECT TOP but only with fixed variables or percents. Any help in how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received.

The beginning code for the make table query is:

INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] )
SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], *
FROM TestBank
WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....