INSERT INTO - wrong fields number error

K

Kamil

Hi.
I'm trying to insert one record as a copy of existing one with some
fields modified.
Result: Run-time error 3346: Number of query values and destination
fields are not the same.
I've counted the records on both sides, and for me everything seems to
be correct... What is the problem then?

sSQL = "INSERT INTO T_SMP_FIFO([KEY], [STDIV_SKU], [SMDINR], [PKTWL],
[SMARNR], [SMCOLR], [SMTYPE], [SMWIDT], [SMLENG], [SMCRMM], [SMCRDD],
[SMCRHH], [SMCRNN], [SMCRSS], [SMCRMS], [SMCRYR], [SMQUAN], [SMKIND],
[SMTEXT], [SMUSID], [SMORNR], [SMRCNR], [SMLINR], [SMWRHS], [SMSUDN],
[SMSUNR], [SMBOOK], [SMINEX], [SMPAPP], [SMLIN9], [DOCNR], [OPTG],
[SMVAL], [HISTPRIC], [Pric_FIFO], [Quant_FIFO], [ZNACZNIK],
[DataCzas], [FLOW], [FORVAL])" & _
"SELECT '" & sNewKey & "',
[STDIV_SKU], [SMDINR], [PKTWL], [SMARNR], [SMCOLR], [SMTYPE],
[SMWIDT], [SMLENG], [SMCRMM], [SMCRDD], [SMCRHH], [SMCRNN], [SMCRSS],
" & iNewMS & ", [SMCRYR]," & _
rsTEMP!smquan & ", " & _
rsTEMP!smkind & ", '" & _
rsTEMP!smtext & "', '" & _
rsTEMP!smusid & "', " & _
rsTEMP!smornr & ", " & _
rsTEMP!smrcnr & ", " & _
rsTEMP!smlinr & ",
[SMWRHS], " & _
rsTEMP!smsudn & ", " & _
rsTEMP!smsunr & ",
[SMBOOK], [SMINEX], [SMPAPP], [SMLIN9], '" & _
rsTEMP!docnr & "', '" & _
rsTEMP!optg & "', " & _
rsTEMP!pric_fifo *
dblCopyQuan & ", " & _
rsTEMP!pric_fifo & ", " &
_
rsTEMP!pric_fifo & ", " &
_
dblCopyQuan & ", '" & _
rsTEMP!znacznik & "',
[DataCzas], '" & _
rsTEMP!FLOW & "', 'YES' AS
FORVAL" & _
"FROM T_SMP_FIFO " & _
"WHERE KEY='" & rsFIFO!Key &
"'"
 
K

Kamil

Hi.
I'm trying to insert one record as a copy of existing one with some
fields modified.
Result: Run-time error 3346: Number of query values and destination
fields are not the same.
I've counted the records on both sides, and for me everything seems to
be correct... What is the problem then?

sSQL = "INSERT INTO T_SMP_FIFO([KEY], [STDIV_SKU], [SMDINR], [PKTWL],
[SMARNR], [SMCOLR], [SMTYPE], [SMWIDT], [SMLENG], [SMCRMM], [SMCRDD],
[SMCRHH], [SMCRNN], [SMCRSS], [SMCRMS], [SMCRYR], [SMQUAN], [SMKIND],
[SMTEXT], [SMUSID], [SMORNR], [SMRCNR], [SMLINR], [SMWRHS], [SMSUDN],
[SMSUNR], [SMBOOK], [SMINEX], [SMPAPP], [SMLIN9], [DOCNR], [OPTG],
[SMVAL], [HISTPRIC], [Pric_FIFO], [Quant_FIFO], [ZNACZNIK],
[DataCzas], [FLOW], [FORVAL])" & _
                                        "SELECT '" & sNewKey & "',
[STDIV_SKU], [SMDINR], [PKTWL], [SMARNR], [SMCOLR], [SMTYPE],
[SMWIDT], [SMLENG], [SMCRMM], [SMCRDD], [SMCRHH], [SMCRNN], [SMCRSS],
" & iNewMS & ", [SMCRYR]," & _
                                            rsTEMP!smquan & ", " & _
                                            rsTEMP!smkind & ", '" & _
                                            rsTEMP!smtext & "', '" & _
                                            rsTEMP!smusid & "', " & _
                                            rsTEMP!smornr & ", " & _
                                            rsTEMP!smrcnr & ", " & _
                                            rsTEMP!smlinr & ",
[SMWRHS], " & _
                                            rsTEMP!smsudn & ", " & _
                                            rsTEMP!smsunr & ",
[SMBOOK], [SMINEX], [SMPAPP], [SMLIN9], '" & _
                                            rsTEMP!docnr & "', '" & _
                                            rsTEMP!optg & "', " & _
                                            rsTEMP!pric_fifo *
dblCopyQuan & ", " & _
                                            rsTEMP!pric_fifo & ", " &
_
                                            rsTEMP!pric_fifo & ", " &
_
                                            dblCopyQuan & ", '" & _
                                            rsTEMP!znacznik & "',
[DataCzas], '" & _
                                            rsTEMP!FLOW & "', 'YES' AS
FORVAL" & _
                                            "FROM T_SMP_FIFO " & _
                                        "WHERE KEY='" & rsFIFO!Key &
"'"
Click to expand...

some of the values which I want to insert are with coma's (for example
17,21). As coma is used to separate fields, then it probably is the
problem. is there a different way to separate fields?
 
K

Kamil

Hi.
I'm trying to insert one record as a copy of existing one with some
fields modified.
Result: Run-time error 3346: Number of query values and destination
fields are not the same.
I've counted the records on both sides, and for me everything seems to
be correct... What is the problem then?
Click to expand...
sSQL = "INSERT INTO T_SMP_FIFO([KEY], [STDIV_SKU], [SMDINR], [PKTWL],
[SMARNR], [SMCOLR], [SMTYPE], [SMWIDT], [SMLENG], [SMCRMM], [SMCRDD],
[SMCRHH], [SMCRNN], [SMCRSS], [SMCRMS], [SMCRYR], [SMQUAN], [SMKIND],
[SMTEXT], [SMUSID], [SMORNR], [SMRCNR], [SMLINR], [SMWRHS], [SMSUDN],
[SMSUNR], [SMBOOK], [SMINEX], [SMPAPP], [SMLIN9], [DOCNR], [OPTG],
[SMVAL], [HISTPRIC], [Pric_FIFO], [Quant_FIFO], [ZNACZNIK],
[DataCzas], [FLOW], [FORVAL])" & _
                                       "SELECT '" & sNewKey & "',
[STDIV_SKU], [SMDINR], [PKTWL], [SMARNR], [SMCOLR], [SMTYPE],
[SMWIDT], [SMLENG], [SMCRMM], [SMCRDD], [SMCRHH], [SMCRNN], [SMCRSS],
" & iNewMS & ", [SMCRYR]," & _
                                           rsTEMP!smquan & ", " & _
                                           rsTEMP!smkind & ", '" & _
                                           rsTEMP!smtext & "', '" & _
                                           rsTEMP!smusid & "', " & _
                                           rsTEMP!smornr & ", " & _
                                           rsTEMP!smrcnr & ", " & _
                                           rsTEMP!smlinr & ",
[SMWRHS], " & _
                                           rsTEMP!smsudn & ", " & _
                                           rsTEMP!smsunr & ",
[SMBOOK], [SMINEX], [SMPAPP], [SMLIN9], '" & _
                                           rsTEMP!docnr & "', '" & _
                                           rsTEMP!optg & "', " & _
                                           rsTEMP!pric_fifo *
dblCopyQuan & ", " & _
                                           rsTEMP!pric_fifo & ", " &
_
                                           rsTEMP!pric_fifo & ", " &
_
                                           dblCopyQuan & ", '" & _
                                           rsTEMP!znacznik & "',
[DataCzas], '" & _
                                           rsTEMP!FLOW & "', 'YES' AS
FORVAL" & _
                                           "FROM T_SMP_FIFO " & _
                                       "WHERE KEY='" & rsFIFO!Key &
"'"
Click to expand...

some of the values which I want to insert are with coma's (for example
17,21). As coma is used to separate fields, then it probably is the
problem. is there a different way to separate fields?
Click to expand...

I've temporarily changed my regional settings from "," to "." and it
works...

How to solve this problem for SQL? should I send these numbers as text
'17,21' and they'll be converted to numbers automatically?
 
J

John Spencer

I get 40 fields in both the Insert clause and the Select Clause so the message
could be bogus.

<<<< You are missing a SPACE before the FROM clause >>>>

Have you tried printing sSQL (Debug.Print sSQL) to the immediate window and
then copying the statement into an blank query? Once there you can attempt to
run it and see if you get a better error message and a better indicator of
where the error is occuring.

sSQL = "INSERT INTO T_SMP_FIFO([KEY], [STDIV_SKU], [SMDINR], [PKTWL],
[SMARNR], [SMCOLR], [SMTYPE], [SMWIDT], [SMLENG], [SMCRMM], [SMCRDD],
[SMCRHH], [SMCRNN], [SMCRSS], [SMCRMS], [SMCRYR], [SMQUAN], [SMKIND],
[SMTEXT], [SMUSID], [SMORNR], [SMRCNR], [SMLINR], [SMWRHS], [SMSUDN],
[SMSUNR], [SMBOOK], [SMINEX], [SMPAPP], [SMLIN9], [DOCNR], [OPTG],
[SMVAL], [HISTPRIC], [Pric_FIFO], [Quant_FIFO], [ZNACZNIK],
[DataCzas], [FLOW], [FORVAL])" & _
"SELECT '" & sNewKey & _
"',[STDIV_SKU], [SMDINR], [PKTWL], [SMARNR], [SMCOLR], [SMTYPE]" & _
", [SMWIDT], [SMLENG], [SMCRMM], [SMCRDD], [SMCRHH], [SMCRNN]" & _
", [SMCRSS]," & iNewMS & ", [SMCRYR]," & _
rsTEMP!smquan & ", " & _
rsTEMP!smkind & ", '" & _
rsTEMP!smtext & "', '" & _
rsTEMP!smusid & "', " & _
rsTEMP!smornr & ", " & _
rsTEMP!smrcnr & ", " & _
rsTEMP!smlinr & ",[SMWRHS], " & _
rsTEMP!smsudn & ", " & _
rsTEMP!smsunr & ",[SMBOOK], [SMINEX], [SMPAPP], [SMLIN9], '" & _
rsTEMP!docnr & "', '" & _
rsTEMP!optg & "', " & _
rsTEMP!pric_fifo * dblCopyQuan & ", " & _
rsTEMP!pric_fifo & ", " & _
rsTEMP!pric_fifo & ", " & _
dblCopyQuan & ", '" & _
rsTEMP!znacznik & "', [DataCzas], '" & _
rsTEMP!FLOW & "', 'YES' AS FORVAL" & _

<<<< Missing SPACE before FROM clause >>>>

"FROM T_SMP_FIFO " & _
"WHERE KEY='" & rsFIFO!Key & "'"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Top