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

    Update query for bit field

    I am using Access 2007 linked to a SQL 2008 database. I created the following update query in Access.

    UPDATE dbo_[Table] SET dbo_[Table].[Field] = 0
    WHERE (((dbo_[Table].[Field])=1));

    [Field] is a bit field, and when I run the query in Select mode, it lists a number of records, and I can edit the resulting dataset manually, but when I run the query in Update mode, it says "You are about to update 0 rows." Why does this not work in Update mode? (I tried using -1 instead of 1 as the criterion but it made no difference.)

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Maybe try [field]<>0?

  3. #3
    New Lounger
    Join Date
    Sep 2011
    Location
    Charmhaven NSW
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts
    im not a sql coder . . maybe [field[=true ???

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your replies. I tried both suggestions for the criterion; i.e., [Field] <>0, [Field] = True, and they both worked.

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Indianapolis, Indiana
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Be careful. I believe that 1 is True and anything else is false. Or is it 0 is False and anything else is True? I always use True and False when working with this type of field.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Did you add that field to the table when there was data in the table? If you did, you will need to run the update query inside SQL Server 2008, i have found that problem in the past with Sql Server 2005.
    Have you set the default value of that field to zero.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    All the fields in the database were created before any data was entered. All bit fields have a default (0 in this case).

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In Access, you can use True and False as conditions or values to be set. However Access displays the value of a True field as -1, even though SQL Server stores it as +1. Thus your test condition of 1 would have certainly failed, but -1 should have worked. No idea what would have made that fail. And it is very important to set the default for any bit fields in SQL Server if you are using ODBC linked tables. Not having it set will cause errors when you try to add a new record.
    Wendell

  9. The Following User Says Thank You to WendellB For This Useful Post:

    Murgatroyd (2011-09-19)

Posting Permissions

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