Problem with syntax in db.execute

[Bilbo_Baggins_Esq]

I added this question to another thread I started last week for a different problem, but since that problem has been resolved, I think it might be better to start a new thread with this new problem.

For some reason, this db.execute statement is generating a "Syntax error in FROM clause" error.

db.Execute _
    "INSERT INTO Description ( Customer_Number, Customer_Type_Name, Amount, Down_Payment, Financed_Amount, Interest_Rate, PPY, Payment, Sale_Date, First_Pay_Date) " & _
    "SELECT Customer_Info.Customer_Number, Customer_Info.Customer_Type_Name, Customer_Info.Sale_Price, Customer_Info.Down_Payment, Customer_Info.Financed_Amount, Customer_Info.Interest_Rate, Customer_Info.PPY, Customer_Info.Payment_Amount, Customer_Info.Sale_Date, Customer_Info.First_Payment_Date " & _
    "FROM Customer_Info " & _
    "(((WHERE Customer_Info.[Customer_Number]) = " & Customer_Number_VBA & "));"

I could easily work around it by pulling a record set from the Customer_Info table, applying the values to variables and then simply add a new record to the description table using a new recordset and applying the set variables.
However, it seemed (at the time) to make more sense just to append the data directly to the Description table.

Any ideas why that FROM clause is firing a systax error?

 

[Rx_]

If you copy the "Select ..... (to the) ;"
Take out the quotes and make it a SQL statement - Past it into a new Query window with SQL view - Replace the "& Customer_Number_VBA & " in the SQL statement with a valid customer number - run the query.
1. Did the query complete OK
2. Did you end up with only 1 record?
3. Do the returned fields from the Select match the Description fields (both order and data types).
If the above works, try a cstr(Customer_Number_VBA)

 

[Rx_]

Pulled up an example of a CurrentDB.Execute example. Your format looks good. Just wanted to look at it against these two examples

       Dim APD_FieldWorkDates_NewID As Long 'The new record that has the record id
       Dim SQLString  As String
10     APD_FieldWorkDates_NewID = Me.Recordset!APD_FieldWorkDates ' the new autocounter ID 
30        SQLString = "DELETE APD_FieldWorkDate_2.*, APD_FieldWorkDate_2.APD_FieldWorkDates FROM APD_FieldWorkDate_2 " & _
            "WHERE (((APD_FieldWorkDate_2.APD_FieldWorkDates)=" & APD_FieldWorkDates_NewID & ")); "
60        CurrentDb.Execute SQLString, dbSeeChanges

' -------------------- another example ------------
170                   SQLString = "INSERT INTO ED_Prospect([Name_Prospect], [ID_State])" & _
                            " VALUES( '" & Trim(Me.txtAddNewFieldProspectName) & _
                             "', '" & ID_StateNo & "');"
180                   CurrentDb.Execute SQLString, dbSeeChanges

 

[Bilbo_Baggins_Esq]

In fact, that is actually how I got the source SQL originally.
However, I actually added the WHERE clause manually.

Just to make sure I did not damage anything, I have taken it back to Access in a blank query in SQL view and got the same error!

So I again created a new query in design view only this time I actually added the WHERE clause and looked at the SQL view.

d'OH!

It's a good thing it wasn't a snake or I'd be dead by now.
I put the darn prens IN FRONT of the WHERE instead of behind it.

I think I've been at this too long, or not long enough...

THANKS FOR THE REMINDER!

 

[Rx_]

Well, I didn't see it either.
My religion requires me to submit what I beleive into the query editor for a test.
So your not alone

Thanks for sharing the success of the process. This site helps me find lots of good 'old' answers that make my day.