INSERT INTO date problem?

[Minty]

I'm trying to code a bulk update to a table with new records by using an INSERT as follows;

 sSQL_Status = ""
    sSQL_Status = sSQL_Status & "INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date], [Repair Type], InvStatus, ETA, RepairStart, [RMA Received Date], ShipPriority ) "
    sSQL_Status = sSQL_Status & "VALUES ( 1194221, 7553, " & sCurrentOrder & ", " & sCurr & ", " & sPartNo & ", " & sManu & ", " & sSerialNo & ", " & sPartNo & ", 25, #" & strDate & "#, " & sRepairType & ", 1, #" & strDate7 & "#, #" & strDate & "#, #" & strDate & "#, 23)"

In the immediate window I get the following;

INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date], [Repair Type], InvStatus, ETA, RepairStart, [RMA ReINSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date], [Repair Type], InvStatus, ETA, RepairStart, [RMA Received Date], ShipPriority ) VALUES ( 1194221, 7553, M65407_1, $, MR-L5MQN-02, Quantum, None, MR-L5MQN-02, 25, #10/14/2013#, "Consignment Stock", 1, #10/21/2013#, #10/14/2013#, #10/14/2013#, 23)

I keep getting an incorrect syntax near '#' error but I thought the dates were formatted correctly. I'm assuming this is another noob beginners error, but can't see it.

Apologies for the awful field names in advance - They can't easily be changed... (Inherited project)

 

[namliam]

Making things readable....

INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date],       [Repair Type], InvStatus,          ETA,  RepairStart, [RMA ReINSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date], [Repair Type], InvStatus, ETA, RepairStart, [RMA Received Date], ShipPriority ) 
                          VALUES ( 1194221,      7553,       M65407_1,          $,    MR-L5MQN-02,      Quantum,            None,                MR-L5MQN-02,             25,      #10/14/2013#, "Consignment Stock",         1, #10/21/2013#, #10/14/2013#, #10/14/2013#, 23)

It looks like you made a pasting error ? :banghead:

fixing the pasting error:

INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date],       [Repair Type], InvStatus,          ETA,  RepairStart, [RMA Received Date], ShipPriority ) 
                          VALUES ( 1194221,      7553,       M65407_1,          $,    MR-L5MQN-02,      Quantum,            None,                MR-L5MQN-02,             25,      #10/14/2013#, "Consignment Stock",         1, #10/21/2013#, #10/14/2013#,        #10/14/2013#, 23)

It looks like you are missing some quotes (") here and there like you are doing at Consignment Stock

 

[DavidAtWork]

Some of your syntax doesn't look quite right, generally follow this

 ' for string variables 
'" & strVar & "'   OR """ & strVar & """
'for numbers
" & numVar & "   OR '" + str(numVar) + "'
'for dates
#" & dateVar & "#   OR '" + format(dateVar) + "'

David

 

[Minty]

Thank you for the replies, my air code was rather hastily written...
I've added the appropriate quote levels and am still getting the same error - The Insert string is now ;

INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date],       [Repair Type], InvStatus,          ETA,  RepairStart, [RMA Received Date], ShipPriority ) 
VALUES (  	     		   1194221,      7553,     "M65407_1",        "$",  "MR-L5MQN-02",    "Quantum",          "None",              "MR-L5MQN-02",             25,      #10/14/2013#, "Consignment Stock",         1, #10/21/2013#, #10/14/2013#,        #10/14/2013#,    23)

And the source (Not so tidy)

sSQL_Status = ""
    sSQL_Status = sSQL_Status & "INSERT INTO [Copy_Job Details] " _
                              & "(Account_No, DelSiteID, [Order Number], " _
                              & "[Currency], [Model Number], Manufacturer, " _
                              & "[Serial Number], [Stock Code / Part Number], [Repair Price], " _
                              & "[RMA Issued Date], [Repair Type], InvStatus, " _
                              & "ETA, RepairStart, [RMA Received Date], " _
                              & "ShipPriority ) " & vbCrLf
    sSQL_Status = sSQL_Status & "VALUES ( 1194221, 7553, """ & sCurrentOrder & """, " & sCurr & ", """ & sPartNo & """, " & sManu & ", " & sSerialNo & ", """ & sPartNo & """, 25, #" & strDate & "#, " & sRepairType & ", 1, #" & strDate7 & "#, #" & strDate & "#, #" & strDate & "#, 23)"

    
    Debug.Print sSQL_Status
    Set cn = New ADODB.Connection
    cn.Open "dsn=Repairs2000"
    cn.Execute (sSQL_Status)
    cn.Close

The error seems to be date related ?

 

[pr2-eugin]

I cannot see anything wrong.. If you copy and paste the generated SQL query into the Query wizard, can you execute it? If there is any error it will point you to the exact error..

Also try.. I can see you used vbCrLf (Why?) Currency a String? (Or double?) your Debug.Print suggests it is a String..

    sSQL_Status = "INSERT INTO [Copy_Job Details] " & _
                  "(Account_No, DelSiteID, [Order Number], " & _
                  "[Currency], [Model Number], Manufacturer, " & _
                  "[Serial Number], [Stock Code / Part Number], [Repair Price], " &  _
                  "[RMA Issued Date], [Repair Type], InvStatus, " & _
                  "ETA, RepairStart, [RMA Received Date], " & _
                  "ShipPriority) VALUES (1194221, 7553, """ & sCurrentOrder & """, """ & _
                  sCurr & """, """ & sPartNo & """, " & sManu & ", " & sSerialNo & ", """ & sPartNo & """, 25, #" & _
                  strDate & "#, " & sRepairType & ", 1, #" & strDate7 & "#, #" & strDate & "#, #" & strDate & "#, 23)"
    Debug.Print sSQL_Status

 

[Minty]

Well I could not see anything wrong - and in the query window my code works...

The currency is a text symbol field required for our accounts system (don't ask) - so string is correct.

The vbCrLf was purely to make the immediate window output more readable...

I'm mystified? The code you posted up is coming up with an error - I'll double check it shortly. ( I got too confused with all the quotes trying to split the lines in the values statement )

Edit: There were an extra set of "" around the currency symbol, once fixed - same net result: Runtime Error '-2147217900 (80040e14)' Incorrect Syntax near '#'

 

[Minty]

UPDATE: This is definitely a date issue, I substituted Now into the first date field and the error changes to "somewhere near 11" which is the current time in the US time zone I'm working remotely into.

 

[namliam]

Is one of the date fields not really a date field?

Can you attach (a dummy version) of your database ? Seems currious

 

[DavidAtWork]

If you've nailed the problem to a date issue, then as nam suggests, check all the date fields are indeed date fields. Otherwise I can only suggest taking it apart, start with just a few fields and keep adding more fields until you find the error.

David

 

[JHB]

INSERT INTO [Copy_Job Details] (Account_No, DelSiteID, [Order Number], [Currency], [Model Number], Manufacturer, [Serial Number], [Stock Code / Part Number], [Repair Price], [RMA Issued Date],       [Repair Type], InvStatus,          ETA,  RepairStart, [RMA Received Date], ShipPriority ) 
VALUES (                      1194221,      7553,     "M65407_1",        "$",  "MR-L5MQN-02",    "Quantum",          "None",              "MR-L5MQN-02",             25,      #10/14/2013#, "Consignment Stock",         1, #10/21/2013#, #10/14/2013#,        #10/14/2013#,    23)

What is the field type for [Serial Number]? I see you are trying to store text value in it, ("None"/None)!

 

[Minty]

Okay - After stripping things right back, I was still getting errors but different ones.

Confession time - I was opening a connection to the wrong DB.... :banghead:
Why it wasn't telling me the table wasn't available I don't know, but it seemed to be connecting!

Apologies for wasting your time on it, I have however learnt a great deal about date formats ....