Code Syntax error

[Rats]

I have used a version of the following piece of code several times to export some data into a subsiduary database and it works fine. I changed it to the following and I keep receiving SYNTAX errors. I have been over it with a fine tooth comb and can't see where the problem lies. The only suspicion I have is that the other times I was using it it was from a table on one DB to a table on the other. In this case I am trying to go from a query to a table. Maybe this is not possible (which I hope is not the case).
Maybe some sharp eyed expert out there can spot the problem.

Thanks in advance

strsql = "INSERT INTO income (ClientID, wages&salary, " & _
            "                       bus&otherincome, rent&investincome)" & _
            "                        " & _
    "IN '" & ServerPath & "' " & _
    "SELECT Clientid, wages&salary, bus&otherincome, rent&investincome " & _
    "FROM  [IncomeTbl Query]" & _
    "WHERE [IncomeTbl Query].planid = " & [Forms]![customers]![PlanID] & ";"

 

[gbat]

Peter

Not sure what version of Access you are using - I am using Access 2000 and it won't let me run a query against a table with a column name that includes the '&' symbol, Access seems to interpret that as a parameter. I would personally avoid using anything but standard alpha-numeric characters when naming columns and tables - ie in your case I would call "bus&otherincome" BusAndOtherIncome.

Also have you tried putting a breakpoint into your code just after you set the strsql variable - then typing ?strsql into the immediate window and hit enter. This will then display the contents of strsql so you can see exactly what statement has been constructed.

Apologies if I'm teaching you to suck eggs.

gbat

 

[Rats]

Thanks GBAT it sounds like you may have hit the nail on the head. I didn't think the & would be a culprit as it was contained within the name. I will change names and see what happens.

Thanks heaps