The actual Access SQL-view code is:

SELECT CHOOSE_Add_Fields.BFY, CHOOSE_Add_Fields.APPN_SYMB, CHOOSE_Add_Fields.SBHD,
CHOOSE_Add_Fields.BCN, CHOOSE_Add_Fields.SA_SX, CHOOSE_Add_Fields.AAA_UIC, CHOOSE_Add_Fields.ACRN,
CHOOSE_Add_Fields.AMT, CHOOSE_Add_Fields.DOC_NUMBER, CHOOSE_Add_Fields.FIPC,
CHOOSE_Add_Fields.REG_NUMB, CHOOSE_Add_Fields.TRAN_TYPE, CHOOSE_Add_Fields.DOV_NUM,
CHOOSE_Add_Fields.PAA, CHOOSE_Add_Fields.COST_CODE, CHOOSE_Add_Fields.OBJ_CODE,
CHOOSE_Add_Fields.EFY, CHOOSE_Add_Fields.REG_MO, CHOOSE_Add_Fields.RPT_MO,
CHOOSE_Add_Fields.EFFEC_DATE, CHOOSE_Add_Fields.Orig_Sort, CHOOSE_Add_Fields.LTrim_BFY,
CHOOSE_Add_Fields.LTrim_AAA, CHOOSE_Add_Fields.LTrim_REG, CHOOSE_Add_Fields.LTrim_DOV,
CHOOSE_Add_Fields.AMT_Rev, CHOOSE_Add_Fields.LTrim_BFY & CHOOSE_Add_Fields.APPN_SYMB &
CHOOSE_Add_Fields.SBHD & CHOOSE_Add_Fields.BCN & CHOOSE_Add_Fields.SA_SX &
CHOOSE_Add_Fields.TRAN_TYPE & CHOOSE_Add_Fields.AMT_Rev AS CONCACT INTO CHOOSERev
FROM CHOOSE_Add_Fields
WHERE (((CHOOSE_Add_Fields.TRAN_TYPE) In ('1K','2D')) AND ((CHOOSE_Add_Fields.LTrim_REG)'7'));

I am not sure what my web browser did to the above code; that said, it is strictly copy/paste

"bcap" wrote:

There is no way to take a recordset and save it as a table in one command.
You would need to first create a new, empty table, and then iterate through
the recordset saving each record in turn.

But, this is completely unnecessary. What you are saying is that this query
works:

SELECT * FROM CHOOSEDATA Where (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And
((CHOOSEData.LTrim_REG)'7'))

In which case, this code will work:

dbs.Execute "SELECT * INTO CHOOSERev FROM CHOOSEDATA Where
(((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))"

There are three possible reasons why the query I gave you earlier didn't
work:

1. There was a line wrap in your newsreader. In your code, every line
except the last one should end with a line continuation character, an
underscore. If this isn't the case, then use the VBA editor to delete the
spurious linewraps caused by your newsreader.

2. The query you are constructing in VBA is just plain different to the
one that works when you run it in the SQL window. Please make sure that the
query in the SQL window still works (without prompting for parameters), and
then post it's SQL.

3. When you ran the query in the SQL window, you erroneously did so in
Tools.mdb, not in Recon.mdb, with the result that the query in the SQL
window and the query in the VBA are running against *different tables* in
*different databases*.


Finally, I'm concerned that you say "I have set the Currentdb to
"Recon.mdb"". This is impossible: CurrentDb is not an object variable, it
is a method, and you cannot set it to anything. It might be a good idea for
you to show us the code you are using to create and set the object variable
"dbs".


wrote in message
.. .
Same error! error (#3601): Too few parameters. Expected 1"

Trust me I am, at least, just as frustrated. What I believe is that the
issue is so obvious that we
are all walking right past it.

Folks, please do not give up!

**************** Please Continue Below
***************************************

The VBA module from which this VBA is invoked, is in "Tools.mdb"

While I am in Tools.mdb, I have set the Currentdb to "Recon.mdb"

In Recon.mdb, I have previously created a table "ChooseData"

I want to use Recon.mdb's "ChooseData" table to create a new table called
"ChooseRev" in Recon.mdb

!!!! PROGRESS !!!! NOTE: The following seems to create something (it does
not bomb!)

Set CHOOSEDRev = dbs.OpenRecordset("SELECT * FROM CHOOSEDATA Where
(((CHOOSEData.TRAN_TYPE) In
('1K','2D')) And ((CHOOSEData.LTrim_REG)'7'))") 'may be a
solution?

If it produces CHOOSERev, a "RecordSet" then what command can I use to
save it in Recon.mdb (alias
dbs and not in Tools.mdb?)

EagleOne

"bcap" wrote:

dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _
& "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX,
CHOOSEData.AAA_UIC, " _
& "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _
& "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _
& "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _
& "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _
& "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _
& "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, "
_
& "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _
& " INTO CHOOSERev FROM CHOOSEData WHERE " & _
& "(((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And
((CHOOSEData.LTrim_REG)'7'));"