Having a strange issue. I have a procedure called from a userform that runs an update query. This runs without issue, but I've included it for reference below:
Code:
DoCmd.RunSQL "UPDATE tblTable1 SET field1=" & Forms!frmName.Field & ", field2=#" & Now & "#, field3=" & value & ", field4=" & DateDiff("s", Now, DLookup("[field8]", "[tblTable1]", "[field6]=" & rs!field6 & " AND [field7]=" & Forms!frmName.field3)) & " AND field5=" & Forms!frmName.Field2 & " WHERE =field6" & rs!field6 & " AND field7=" & Forms!frmName.field3
When I pause the procure on this line & print the SQL, it returns:
UPDATE tblTable1 SET field1=4, field2=#2/11/2019 12:04:38 PM#, field3=4, field4=6625 AND field5=8 WHERE field6=8 AND field7=28566
Looks good to me, so it seems the code runs correctly. However, if I paste this SQL into a query and run it, I get:
tblTable1
Field1 |
Field 2 |
Field3 |
Field4 |
4 |
2/11/2019 12:01:16 PM |
4 |
-1 |
Very unexpected to see -1 for Field4 there. I would expect to see 6625, as that is what the SQL lists. In table tblTable1, field4 is a LONG INTEGER data type with a default value of 0.
Just for testing, I tried pasting each of these SQL statements into queries:
Code:
UPDATE tblTable1 SET field1=4, field2=#2/11/2019 12:04:38 PM#, field3=4, field4=0 AND field5=8 WHERE field6=8 AND field7=28566
UPDATE tblTable1 SET field1=4, field2=#2/11/2019 12:04:38 PM#, field3=4, field4=1 AND field5=8 WHERE field6=8 AND field7=28566
UPDATE tblTable1 SET field1=4, field2=#2/11/2019 12:04:38 PM#, field3=4, field4=-1 AND field5=8 WHERE field6=8 AND field7=28566
UPDATE tblTable1 SET field1=4, field2=#2/11/2019 12:04:38 PM#, field3=4, field4=NULL AND field5=8 WHERE field6=8 AND field7=28566
UPDATE tblTable1 SET field1=4, field2=#2/11/2019 12:04:38 PM#, field3=4, field4="potato" AND field5=8 WHERE field6=8 AND field7=28566
UPDATE tblTable1 SET field1=4, field2=#2/11/2019 12:04:38 PM#, field3=4, field4=TRUE AND field5=8 WHERE field6=8 AND field7=28566
UPDATE tblTable1 SET field1=4, field2=#2/11/2019 12:04:38 PM#, field3=4, field4=FALSE AND field5=8 WHERE field6=8 AND field7=28566
All of them return the same thing: -1.If I execute the update query, the table is updated properly, but with the incorrect value of -1 for Field4. I can manually adjust this field to any integer I like without issue.
Any insight into what may be causing this issue? Thanks in advance.