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
Jerry Whittle - 19 Apr 2006 17:46 GMT
Should be a SELECT INTO and look something like:
SELECT Admin2.Name, Admin2.Number
INTO AdminNew
FROM Admin2;

Signature
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> I am trying to use TOP N to create a table and access does not like my code
> can someone help me.
[quoted text clipped - 11 lines]
>
> Thank you
Hal2604 - 19 Apr 2006 18:37 GMT
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!
John Spencer - 19 Apr 2006 20:30 GMT
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.
>I am trying to use TOP N to create a table and access does not like my code
> can someone help me.
[quoted text clipped - 13 lines]
>
> Thank you
Hal2604 - 20 Apr 2006 12:08 GMT
Thank you for your help. You fixed my problem. It was the missing spaces.
It works wonderfully now.
Holly
John Spencer - 20 Apr 2006 13:10 GMT
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.
> Thank you for your help. You fixed my problem. It was the missing
> spaces.
> It works wonderfully now.
>
> Holly