Hello All,

I am trying to run a query in a VBA Code module (in Access
97), and I keep getting the following error:

Query input must contain at least one table or query.

The problem is, the SQL will run if it's in a normal
query, but not when I put the SQL in a vba module.  I have
other SQL statements running in this same VBA module.  
Here is the SQL I am trying to run:

DoCmd.RunSQL "INSERT INTO tblTempMTDSales ( ITEM_ID,
PUB_NUM_ID, DC_CD, DC_SHRT_DN, MTDSales )" & _
        "SELECT IT_ITEM.ITEM_ID, IT_ITEM.PUB_NUM_ID,
IT_DC_ITEM_SALE.DC_CD, BP_DC.DC_SHRT_DN," & _
        "Sum(IT_DC_ITEM_SALE.SHIP_UNIT_CNT) AS MTDSales" &
_
        "FROM IT_ITEM INNER JOIN ((IT_DC_ITEM_SALE INNER
JOIN IT_DC_ITEM ON" & _
        "(IT_DC_ITEM_SALE.ITEM_ID = IT_DC_ITEM.ITEM_ID)
AND (IT_DC_ITEM_SALE.DC_CD = IT_DC_ITEM.DC_CD))" & _
        "INNER JOIN BP_DC ON IT_DC_ITEM.DC_CD =
BP_DC.DC_CD) ON IT_ITEM.ITEM_ID = IT_DC_ITEM_SALE.ITEM_ID"
& _
        "WHERE (((IT_DC_ITEM_SALE.DC_CD)='DD' Or
(IT_DC_ITEM_SALE.DC_CD)='EE' Or (IT_DC_ITEM_SALE.DC_CD)
='NV'" & _
        "Or (IT_DC_ITEM_SALE.DC_CD)='CI') AND" & _
        "((IT_DC_ITEM_SALE.BUS_YY_ID)=(SELECT
DB_Sale_Rpt_Cntl_dt.Bus_yy_Id FROM db_sale_rpt_cntl_dt))"
& _
        "AND ((IT_DC_ITEM_SALE.BUS_PRD_ID)=(SELECT
db_sale_rpt_cntl_dt.bus_prd_id FROM
db_sale_rpt_cntl_dt)))" & _
        "GROUP BY IT_ITEM.ITEM_ID, IT_ITEM.PUB_NUM_ID,
IT_DC_ITEM_SALE.DC_CD, BP_DC.DC_SHRT_DN" & _
        "HAVING (((IT_ITEM.PUB_NUM_ID)='B333'));"

I can't understand why it will run when in a query, but
not when it's in the module.

Any ideas would be greatly appreciated as this is DRIVING
ME CRAZY!!!!

Thanks,
Sandi Baker

some of your lines need an extra space (I usually add an extra space at
start and end of all lines.)

example:

DoCmd.RunSQL "INSERT INTO tblTempMTDSales ( ITEM_ID, PUB_NUM_ID, DC_CD,
DC_SHRT_DN, MTDSales )" & _
        "SELECT IT_ITEM.ITEM_ID, IT_ITEM.PUB_NUM_ID, IT_DC_ITEM_SALE.DC_CD,
BP_DC.DC_SHRT_DN," & _

Here the "SELECT IT_ITEM.ITEM_ID, ....  is right next to the MTDSales )" on
the line above it.

Therefore access reads it as

MTDSales )SELECT IT_ITEM.ITEM_ID, ....

Just add an extra space at the beginning and the end of each line (just to
be safe). and run it again.

Hope it helps,

Rodrigo.