passing a variable to an append query

[Baroj Von Reich]

Hope some one can assist.

All I want to do is pass 2 vaiarbles to an SQL script

Here is the sql Script
--------------------------
SQL_Script_ACCT_1 = "INSERT INTO [OUTPUT - SIEBEL_ACCT_NUM] ( REPORT_MONTH, GL_CENTER, GL_ACCOUNT " & _
", ID_TYPE, ID_VALUE, ELEMENT_ID, Siebel_Acct_Num " & _
", BILLED_REVENUE, BBUE_REVENUE, EBUB_ACCRUAL, BBUE_REVERSAL, EBUB_REVERSAL ,NET_MONTHLY_REVENUE ) " & _
"SELECT " & Month_Date & " AS RMONTH, [" & Table_Month_File & "].GL_CENTER " & _
", [" & Table_Month_File & "].GL_ACCOUNT, [" & Table_Month_File & "].ID_TYPE, [" & Table_Month_File & "].ID_VALUE " & _
", [" & Table_Month_File & "].ELEMENT_ID " & _
", [" & Table_Month_File & "].Siebel_Acct_Num " & _


", [" & Table_Month_File & "].BILLED_REVENUE, [" & Table_Month_File & "].BBUE_REVENUE " & _
", [" & Table_Month_File & "].EBUB_ACCRUAL, [" & Table_Month_File & "].BBUE_REVERSAL " & _
", [" & Table_Month_File & "].EBUB_REVERSAL, [" & Table_Month_File & "].NET_MONTHLY_REVENUE " & _
"FROM " & Table_Month_File & " INNER JOIN PARAMETER ON [" & Table_Month_File & "].Siebel_Acct_Num = PARAMETER.SIEBEL_ACCT_NUM;"

--------
here is the code that sets up the data and calls the SQL Script

Table_Month_File = Table_Month_Year & "_" & Table_Month_Counter (The value will look somthing like this
"11_07_01"

Month_Date = "#5/1/2008#"
DoCmd.RunSQL (SQL_Script_ACCT_1)

I keep getting an error like.

what am I doing wrong ??

Frank

[June7]

This might be part of the issue.
On the 5th and last lines, missing a space before & in [" & Table_Month_File& "]

[Baroj Von Reich]

Hello Jun 7,

Unfortunately it was me not pasting correctly

[June7]

I can't see anything else that would be a problem but I have never used the PARAMETER syntax. It is my understanding you must declare the PARAMETER. You don't show that code.

You could try building the SQL in stages and see if that helps debug.

strSQL = "SELECT ..."
strSQL = strSQL & "..."
etc.

[rpeare]

It looks to me like you're pulling information from a new table each month? If that's the case you're using a non-normalized database. A non-normalized database is much more difficult to handle than a normalize database as you're finding out.

In your code put in a line before you try to execute the SQL code

debug.print SQL_Script_Acct1

See what your code is actually generating for a statement (post it here too) and also what exactly is the error you're getting when you attempt to run the script?