0

Both Table A and Table B have a field called [Expiration Date] where the data type is datetime and allow NULLs. My goal is to read Table A, get the value of this field, and write it to Table B.

I read the field value likes this, this part is fine:

strSQL = "SELECT a.*, b.[Model Number], b.CleaningCode, b.[Type] " & _
            "FROM wReceiveLabel AS a " & _
            "LEFT OUTER JOIN [Item Master] AS b ON a.Part = b.[AP Part Number] " & _
            "WHERE a.Workstation = '" & UniqueID() & "' " & _
            "AND a.Received > 0"
rs.Open strSQL, con, adOpenStatic, adLockReadOnly

Then, I try to write the value into Table B, but this doesn't work:

Dim dateExpirationDate As Variant
dateExpirationDate = rs![Expiration Date]

strSQL = "INSERT INTO [PO Receipts] ([Lot Number], [Date Received], [Part Number], [Rev], [Purchase Order], POLine, [Vendor Code], " & _
                  "[Total Received], [Sample ID Only], [Sample Level S], [Sample Level I], [Sample Level II], [Sample Level III], " & _
                  "[Accept Quantity], [Reject Quantity], Inspector, Remarks, [Type], PackList, FAI, [Expiration Date], [Heat Code]) VALUES (" & _
                  "'" & strLot & "', '" & [Receive Date] & "', '" & rs!Part & "', '" & rs!Rev & "', '" & rs!PurchaseOrder & "', '" & rs!Line & "', " & _
                  "'" & [Vendor Code] & "', " & rs!Received & ", " & Format(CInt(rs!IDOnly)) & ", " & rs!LevelS & ", " & _
                  rs!LevelI & ", " & rs!LevelII & ", " & rs!LevelIII & ", " & _
                  rs!Accepted & ", " & rs!Rejected & ", '" & [Inspector] & "', '" & rs!Remarks & "', '1', '" & [cboPL] & "', " & rs!FAI & ", " & dateExpirationDate & ", '" & rs![Heat Code] & "')" 
con.Execute strSQL, , adCmdText + adExecuteNoRecords

It gives an error that says Incorrect syntax near ','

braX
  • 11,506
  • 5
  • 20
  • 33
whatwhatwhat
  • 1,991
  • 4
  • 31
  • 50
  • 1
    What does `Debug.Print strSQL` look like when the insert fails? – Tim Williams Jan 06 '23 at 23:15
  • Why so much duplication of data? – June7 Jan 06 '23 at 23:22
  • Or might be able to use INSERT SELECT syntax. And another approach is to open a recordset of destination table and use AddNew method https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/recordset-addnew-method-dao – June7 Jan 07 '23 at 03:34
  • Detect which fields can be null, and substitute `NULL` for the value (no quotes). – braX Jan 07 '23 at 03:44
  • Use can apply my function [CSql](https://stackoverflow.com/a/43591778/3527297). – Gustav Jan 07 '23 at 10:00

0 Answers0