Comma (,) is causing me trouble on table INSERT 1

[zyrag]

I have a [Customer Name] field from table1 and is included for my INSERT routine into table2. The error msg during the INSERT is "-2147217900 - No. of query values and destination are not the same."

I have checked the no. of fields in the INSERT phrase against the no. of values in the VALUES phrase and got sure that the pair is perfect. But when i did a debug.print on my SQL statement, it revealed that the comma in the [Customer Name] might have caused the impairing of the INSERT and the VALUES phrases. [Customer Name] contains comma between lastname and firstname. If i remove the pairs (INSERT and VALUES) for customer name, the routine works fine.

But i need to insert the customer name. How do i achieve this?

TIA,

 

[SonOfEmidec1100]

Comma should not cause a problem. This error is caused by mismatched field list between the SELECT and INSERT (example below). Puzzled by your statement that you can see the name has a comma by looking at the sql statement

Can you post the results of your debug.print?

INSERT INTO table2 ( Acct_id, mydate,  DATA )
SELECT Acct_id, mydate,[highlight] SeqNo[/highlight], DATA
FROM Table1

 

[zyrag]

Got it working now. Thanks anyway.. What makes the error is the lack of "'" in between customer_name field..

strSQL="INSERT INTO tbl1 (RefID, Occupant, CurrReadDate, CurrVal) " _ 
& "VALUES (" & lngRefID & ",[COLOR=red]'[/color]" & !Customer_Name & "[COLOR=red]'[/color],#" & !LastReadDate & "#," & PrevVal & ")"

 

[zemp]

Comma problems, see faq709-1526.

zemp