I have an Access database Module that draws random records from a 3 table query and inserts them into a new table

I get an SQL error regarding operator syntax when the module is run (error details to follow). The SQL code worked fine when drawing records from one table but when I modified the code to draw from 3 tables I get the message "Syntax Error:missing operator in Query expression tblTemp.CorrectSelection INTO tblRandom_June10...."

Below is a portion of the module code where the error was identified:

-------------------------------------------------------------------------------------------------------------------------------------------------------
strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
strSQL = "SELECT Top " & TopValue & " tblTemp.SubjectID, tblTemp.SubjectName, tblTemp.QuestionID, " & _
"tblTemp.QuestionAndOrInstructions, tblTemp.MultipleAnswerID, tblTemp.MultipleAnswerDescription, " & _
"tblTemp.UserSelection, tblTemp.CorrectSelection" & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
------------------------------------------------------------------------------------------------------------------------------------------------------------------

Any help would be greatly appreciated-----Below is the complete module code in case it is needed to isolate the problem------Thanks SCOTT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Option Compare Database



Option Explicit

Sub PickRandom()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strTopRecords As String
Dim TopValue As Integer
TopValue = 0

strTopRecords = InputBox("Enter the desired number of Records", _
"Number of Questions", _
"1-100")
TopValue = Val(strTopRecords)


Dim strTableName As String

strSQL = "SELECT CInt(tblSubjects.SubjectID) AS SubjectID, tblSubjects.SubjectName, " & _
" CInt(tblQuestions.QuestionID) AS QuestionID, tblQuestions.QuestionAndOrInstructions, " & _
" tbleMultipleAnswers.MultipleAnswerID, tbleMultipleAnswers.MultipleAnswerDescription, " & _
" tbleMultipleAnswers.UserSelection, tbleMultipleAnswers.CorrectSelection " & _
"INTO tblTemp " & _
"FROM (tblSubjects INNER JOIN tblQuestions ON tblSubjects.SubjectID = tblQuestions.fk_SubjectID)" & _
"INNER JOIN tbleMultipleAnswers ON tblQuestions.QuestionID = tbleMultipleAnswers.fk_QuestionID;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Set db = CurrentDb()
Set tdf = db.TableDefs("tblTemp")
Set fld = tdf.CreateField("RandomNumber", dbDouble)

tdf.Fields.Append fld

Set rst = db.OpenRecordset("tblTemp", dbOpenTable)

rst.MoveFirst
Do
Randomize
rst.Edit
rst![RandomNumber] = Rnd()
rst.Update
rst.MoveNext
Loop Until rst.EOF

rst.Close
Set rst = Nothing

strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
strSQL = "SELECT Top " & TopValue & " tblTemp.SubjectID, tblTemp.SubjectName, tblTemp.QuestionID, " & _
"tblTemp.QuestionAndOrInstructions, tblTemp.MultipleAnswerID, tblTemp.MultipleAnswerDescription, " & _
"tblTemp.UserSelection, tblTemp.CorrectSelection" & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"ORDER BY tblTemp.RandomNumber;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

db.TableDefs.Delete ("tblTemp")
End Sub

I don't think you can use the TOP predicate in a "SELECT ... INTO ..." SQL.

Acccording to Access Help, the syntax is:

,i.e. not any type of predicate in the SELECT clause.

Try using a SubSQL (which include the TOP predicate + ORDER BY clause) as the "source" according to the above syntax.

I'd say it should be pretty much as you were as far as SELECT clause syntax. (i.e. any valid SELECT statement, including TOP).
Looking at your code - the error is probably more insideous and yet far more common.

"tblTemp.UserSelection, tblTemp.CorrectSelection" & _
"INTO " & strTableName & " " & _

There's no space after the last field and before the INTO clause. :-)

Cheers.

>>any valid SELECT statement, including TOP<<

Thanks for the correction, Leigh.

After I posted (I don't use Make-Table/SELECT...INTO... to crerate Table + data so I check Help), I thought the syntax I got from Help was a bit too simplistic. I was going to check out this morning but saw your post first.

Cheers

Lpurvis----you're the man!! Thanks, your suggestion worked. What I'm curious about is why the space was needed between the quotation mark and the INTO clause when it is not needed in the SELECT and FROM Clauses? And also why is the space needed after the last field and the Quotation mark when it isn't needed elsewhere?

I'm relatively new to SQL programming so am just trying to learn the "Whys" whenever possible although I realize that sometimes there just isn't any logical reason.

Thanks again for your help, I knew it was probably something simple.

Scott

It's not so much that you should worry about when you can leave a space or not. I couldn't reel off a comprehensive list of which require at least a space and which don't - I'd be guessing for some of it.
Required clauses will be excused when it's obvious to the parser.
e.g.
SELECT *FROM TableName
SELECT [FieldName]FROM TableName
SELECT *FROM [TableName]WHERE FieldName < 10

But just forget about that.
Construct your SQL statements in a solid format. Always separate your clauses. Always. There's just no reason not to.
All things being equal, I'd like a carriage return between clauses (that's not as necessary in code constructed SQL as there's every chance you won't ever gaze upon the SQL) but if you always work with your clauses kept sensibly distinct then you'll never encounter this problem. (Obviously everyone makes typos when coding from time to time - but you also can then debug those mistakes at a glance.)

Cheers.