EMERGENCY - what is wrong with this SQL statement?? 2

[nimarii]

Hello - I can't figure out why the following sql statement is causing this error:

Run-time error '3346': Number of query values and destinatino fields are not the same.

Here's the sql statement(its really long, 20 column fields, and 20 values...i define the variables used earlier in the code..)

    strSQL = "INSERT INTO RFPCustomer(RFPID, ContactName, ContactNumber, ContactEmail, DateOrderReceivedOn, RequestedDueDate, FOCDate, TurnUpDate, InstallDate, OrderNumber, Interval_CID, Service_CID, DiscDueDate, Coding_CID, Framing_CID, ConnectorType_CID, LocalContactA, LocalContactB, contactnumbera, contactnumberb) VALUES('" & rs("rfpnumber") & "', '" & Nz(rs("ordercontactname"), "") & "', '" & Nz(rs("ordercontactnumber"), "") & "', '" & Nz(rs("ordercontactemail"), "") & "', '" & Nz(rs("orderreceivedon"), "") & "'" & _
    "'" & Nz(rs("requestedduedate"), "") & "', '" & Nz(rs("finalfocdate"), "") & "', '" & Nz(rs("turnedupdate"), "") & "', '" & Nz(rs("installdate"), "") & "', '" & Nz(rs("customerordernumber"), "") & "', '" & interval_cid & "', '" & service_cid & "', '" & Nz(rs("plantdisconnectduedate"), "") & "', '" & coding_cid & "', '" & framing_cid & "', '" & connectortype_cid & "', '" & Nz(rs("localcontacta"), "") & "', '" & Nz(rs("localcontactz"), "") & "', '" & Nz(rs("contactnumbera"), "") & "', '" & Nz(rs("contactnumberz"), "") & "')"
    DoCmd.RunSQL (strSQL)

apparently, the issue is not with the number of fields, because they match, but i dont know what else to check for...is the NZ function throwing things off??

any advice is super welcome!

 

[Zion7]

nimarii, where you use the line separator _

you're missing a comma...

...(rs("orderreceivedon"), "") & "'" & _
"'" & Nz(rs("requestedduedate"), "") & ....

should be
...(rs("orderreceivedon"), "") & "'," & _
"'" & Nz(rs("requestedduedate"), "") & ....

but less redundantly..

...(rs("orderreceivedon"), "") & "','" & _
Nz(rs("requestedduedate"), "") & ....

 

[nimarii]

thank you sooo much!!!
however - i still get an error

Run-time error '3134':
Syntax error in INSERT INTO statement.

here's the code...:

    DoCmd.RunSQL ("INSERT INTO RFPCustomer(RFPID, ContactName, ContactNumber, ContactEmail, DateOrderReceivedOn, RequestedDueDate, FOCDate, TurnUpDate, InstallDate, OrderNumber, Interval_CID, Service_CID, DiscDueDate, Coding_CID, Framing_CID, ConnectorType_CID, LocalContactA, LocalContactB, contactnumbera, contactnumberb) VALUES('" & rs("rfpnumber") & "', '" & Nz(rs("ordercontactname"), "") & "', '" & Nz(rs("ordercontactnumber"), "") & "', '" & Nz(rs("ordercontactemail"), "") & "', '" & Nz(rs("orderreceivedon"), "") & "', " & _
    "'" & Nz(rs("requestedduedate"), #1/1/2001#) & "', '" & Nz(rs("finalfocdate"), #1/1/2001#) & "', '" & Nz(rs("turnedupdate"), #1/1/2001#) & "', '" & Nz(rs("installdate"), #1/1/2001#) & "', '" & Nz(rs("customerordernumber"), "") & "', '" & intervalcid & "', '" & servicecid & "', '" & Nz(rs("plantdisconnectduedate"), #1/1/2001#) & "', '" & codingcid & "', '" & framingcid & "', '" & connectortypecid & "', '" & Nz(rs("localcontacta"), "") & "', '" & Nz(rs("localcontactz"), "") & "', '" & Nz(rs("contactnumbera"), "") & "', '" & Nz(rs("contactnumberz"), "") & "'")

any ideas...? thanks again for helping me!!!

 

[Zion7]

your final statement...

& "', '" & Nz(rs("contactnumberz"), "") & "'")

should be

& "', '" & Nz(rs("contactnumberz"), "") & "')"

notice where the last double quote is

...do dates need hashmarks "#" or single quotes????

"#" & Nz(rs("requestedduedate"), #1/1/2001#) & "#, #"

(I'm not positive, but asking)

 

[Zion7]

...Actually NOTHING for dates & numbers & variables...

... ,Nz(rs("requestedduedate"), #1/1/2001#) , Nz(rs("finalfocdate"), #1/1/2001#) , Nz(rs("turnedupdate"), #1/1/2001#), ....

 

[nimarii]

it works!!! you've saved my life - thank you so much

 

[Zion7]

a pleasure nimarii...