Results 1 to 5 of 5
  1. #1
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47

    Unexpected UPDATE Query Behavior - Incorrect Value Returned

    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.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you should NOT use Dlookups in a query. They are fine for forms/reports, but the QUERY is the dlookup.
    join the tables needed in the query to get the lookup.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Fields 4 and 5 should be separated by a comma, not AND. You're causing a Boolean result.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47
    Pah, of course. Thanks.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. I agree with ranman about the DLookup(). Normally you'd use a join to get a related value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-21-2018, 08:44 PM
  2. Split database - unexpected behavior
    By Lynn Cohen in forum Database Design
    Replies: 5
    Last Post: 09-16-2016, 06:53 AM
  3. Another unexpected parameter query
    By Access_Novice in forum Queries
    Replies: 2
    Last Post: 01-14-2016, 12:30 AM
  4. Replies: 2
    Last Post: 09-25-2013, 12:40 PM
  5. Replies: 3
    Last Post: 12-26-2011, 10:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums