Trying to create a table with a query in sql

H

Hal2604

I am trying to use TOP N to create a table and access does not like my code
can someone help me.

Dim Num As Long
Num = 2
DoCmd.RunSQL "INSERT INTO tblRptFreeSpotList(LastName, FirstName,
EmailAddr, ParkingLot, PreferLot, DH)" & _
"SELECT TOP " & Num & " MasterList.LastName, MasterList.FirstName,
MasterList.EmailAddr, MasterList.ParkingLot, MasterList.PreferLot, MasterList.
DH FROM MasterList" & _
"WHERE MasterList.PreferLot = " & "'" & Forms!frmMain!txtPreferLot &
"'" & _
"ORDER BY MasterList.DH"

Thank you
 
J

Jerry Whittle

Should be a SELECT INTO and look something like:

SELECT Admin2.Name, Admin2.Number
INTO AdminNew
FROM Admin2;
 
H

Hal2604

How do I get the top values? The statement you gave me doesn't keep the top
values. The error I am getting when I run it in access says "Syntax error in
From Clause"

Thanks!
 
J

John Spencer

Assumption is that you are getting a syntax error. You really need to tell
us what error messages you are getting rather than "Access does not like my
code".

Missing a space between tblRptFreeSpotList and the list of fields
Missing a space between the list of fields and SELECT
Missing a space between MasterList and WHERE
Missing a space before ORDER BY (by the way, the ORDER BY is probably
useless in an insert query)

Dim Num As Long, StrSQL as String
Num = 2

StrSQL = "INSERT INTO tblRptFreeSpotList (LastName, FirstName,
EmailAddr, ParkingLot, PreferLot, DH)" & _
" SELECT TOP " & Num & " MasterList.LastName, MasterList.FirstName,
MasterList.EmailAddr, MasterList.ParkingLot, MasterList.PreferLot,
MasterList.DH FROM MasterList" & _
" WHERE MasterList.PreferLot = " & "'" & Forms!frmMain!txtPreferLot
&
"'" & _
" ORDER BY MasterList.DH"

DoCmd.RunSQL StrSQL

There may be other problems in the SQL statement. I find it is best for me
to build the SQL statement in a variable and then Debug.print it to see if
it looks correct. IF it does, I copy it into a new query and attempt to run
it. If it runs, I comment out the debug.print strSQL statement and let code
run.
 
H

Hal2604 via AccessMonster.com

Thank you for your help. You fixed my problem. It was the missing spaces.
It works wonderfully now.

Holly
 
J

John Spencer

I'm glad that fixed the problem. And please remember the advice on how to
test your SQL statement. It will end up saving you a lot of headaches.
 
Top