Trying to create a table with a query in sql

  • Thread starter Thread starter Hal2604
  • Start date Start date
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
 
Should be a SELECT INTO and look something like:

SELECT Admin2.Name, Admin2.Number
INTO AdminNew
FROM Admin2;
 
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!
 
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.
 
Thank you for your help. You fixed my problem. It was the missing spaces.
It works wonderfully now.

Holly
 
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.
 
Back
Top