Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Update query (Access 2000)

    I have a table with a bit field that contains nulls in some records and this is causing spurious write conflict messages, so I have created an update query to change the nulls in the bit field to zeros, and when I run it as a select query it lists all the relevant records, but when I run it as an update query it says there are zero rows to update. What is causing this? Here is the SQL code.

    SELECT Table.Field FROM Table WHERE (((Table.Field) Is Null));

    UPDATE Table SET Table.Field = 0 WHERE (((Table.Field) Is Null));

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (Access 2000)

    What exactly do you mean by a "bit field"? That is not a native data type in Access.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Update query (Access 2000)

    Edited by HansV to porovide link to URL - see <!help=19>Help 19<!/help>

    It is a bit field in a linked SQL table. It is listed as a Yes/No field in the Access table properties.

    http://support.microsoft.com/kb/280730/EN-US/

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (Access 2000)

    It would have been useful if you had mentioned in your original post that your table is a linked SQL Server table, it is relevant information. It is difficult to answer questions if you give us only part of the picture.

    Hopefully someone who uses SQL Server will be able to help.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Update query (Access 2000)

    Sorry, I didn't realize that was relevant information. I tried using the same code in an update query in SQL Enterprise Manager and it worked OK there.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (Access 2000)

    I assume you don't need to run it from Access any more then. If you need to in the future, try creating a pass-through query; this will be run on the server instead of in Access. Look up pass-through query in the online help to find out how to create one.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Update query (Access 2000)

    The ODBC driver you are using to connect to SQL Server doesn't know about the fact that a bit field in SQL Server can have an "indeterminate" value and be treated as a Null condition. Therefore if you are using SQL Server tables with bit fields you should always define the table as having a default value (presumably 0 or False), and that it is a required field. Otherwise you get into issues with updatability and write conflicts. Also be aware that SQL Server returns a True value as 1 (+1), while Access returns a value of -1 for a True condition. Hope this helps clarify things.
    Wendell

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Update query (Access 2000)

    Thanks for your reply.

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Update query (Access 2000)

    Thanks for this reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •