using temp variable to append specific number of records to table (1 Viewer)

[prof.fred]

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. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).

The beginning code for the make table query (where 25 is the number of records added) 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 .....

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. (This process then is repeated for a total of 7 append tables with different temp variables.)

 

[CJ_London]

you can't do this from a query definition, you would need to build and run from VBA

e.g.

dim sqlstr as string
 
sqlstr="INSERT INTO [Output] ( RndNo,... ... SELECT TOP[COLOR=red] " & somevalue & "[/COLOR] TestBank.RndNo, TestBank.PointBiserial, TestBank.....
currentdb.execute(sqlstr)

)

 

[BlueIshDan]

[erased for being dumb]

 

[spikepl]

The "25" is not a parameter you can pass to a query in any way. You can (re.-)construct a query on the fly using QueryDef.SQL (google that), or just db.OpenRecordset(yourQueryStringConcoctedForTheOccasion, other params)

db.Execute, naturellement!

 

[prof.fred]

This is where my lack of any formal 'training' in Access limits me. I understand where to insert VBA code in events using the design mode of reports and such, but not when events are not specified. Using the first suggestion of inserting the variable in a string using "dim sqlstr as string" inserting the variables then running the code (or so I assume) with the "currentdb.execute(sqlstr)', I get a "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'." I assume I am not running the code in the correct sequence or location, but that is where I am lost. The code works fine when a specific number or percent is used instead of a variable.

Below is the code as I assume it should be based on the recommendation using a temp variables. The process is repeated with 7 different temp variables to build the final table 'Output'. Please correct me on the code and also how to run it in the correct way to pull the records into a new table:


dim sqlstr as string

sqlstr="INSERT INTO [Output] (RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] )
SELECT TOP " & [TempVars]![TempK&SA1] & " 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 [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.Exam1)=[TempVars]![TempExam]) AND ((TestBank.Status)=[TempVars]![TempStatus]) AND ((TestBank.[NCCPAKnowledge&Skills])=5)) 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.Status)=[TempVars]![TempStatus]) AND ((TestBank.Exam2)=[TempVars]![TempExam]) AND ((TestBank.[NCCPAKnowledge&Skills])=5)) 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.Status)=[TempVars]![TempStatus]) AND ((TestBank.Exam3)=[TempVars]![TempExam]) AND ((TestBank.[NCCPAKnowledge&Skills])=5)) 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.Status)=[TempVars]![TempStatus]) AND ((TestBank.Exam4)=[TempVars]![TempExam]) AND ((TestBank.[NCCPAKnowledge&Skills])=5))
ORDER BY TestBank.RndNo;"

currentdb.execute(sqlstr)


Thanks for any direction you can offer. This is that last task to put this test bank together so that we can generate more appropriate exams for the students in the program where I teach.

 

[stopher]

I'm pretty sure you can do all this using just queries.

In order to get the Top 25 where 25 is a variable, instead apply a ranking column to your source data (search on ranking queries to check how to do this). So once you have ranked your source data from say 1 to 3000 then you can apply your variable through the where clause where rank <= [enter number of Records].

Or maybe I misunderstood the problem.

 

[spikepl]

For debugging sql read carefully and follow this: http://www.baldyweb.com/ImmediateWindow.htm

For constructing SQL on the fly see this: http://allenbrowne.com/ser-62.html
Pay attention to the code example provided in a link in that article

 

[CJ_London]

I get a "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'." I assume I am not running the code in the correct sequence or location, but that is where I am lost. The code works fine when a specific number or percent is used instead of a variable.

Have you assigned a valid value to [TempVars]![TempK&SA1]?

but not when events are not specified

The event required is dependant on your requirements - it could be in the on clickevent of a button on a form, a double cick on some other control it could be the before update or close event event of the form for example.

Without having any understanding of how you want this to work it is impossible to make a clearer suggestion.