Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Bit Column In A Query Where Clause

    Hi,

    I have a Query in my access database which is the data source of one of my Reports. Part of the Where clause of this Query looks like the following:
    Code:
    WHERE
         (MyTable1.MyCol1=0)
    This MyTable1.MyCol1 column is of type
    Code:
    bit
    and has a
    Code:
    not null
    constraint in the backend database.
    My question is that why is it when I run the report, I get a pop-up dialog that asks me for a value of this column before it proceeds. If I don't put anything in this dialog my result comes empty. If I put 0 then I get the expected result.
    There is another condition in the Where clause of the same pattern but the datatype of the column is
    Code:
    Flag(bit)
    and has also a not-null constraint. This clause doesn't cause any 'pop-up dialog problem' as above.

    Thank you in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you post the SQL?
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    The Query inside my MS Access database looks like the following:

    Code:
    SELECT 
    	dbo_MyTab1.MyCol4, dbo_MyTab2.MyCol3
    FROM 
    	dbo_MyTab2 INNER JOIN dbo_MyTab1
    		ON dbo_MyTab2.MyCol2 = dbo_MyTab1.MyCol2
    WHERE 
        (((dbo_MyTab1.MyCol1)=0)
    	AND dbo_MyTab1.MyCol3 NOT LIKE '%Expr%')
        AND ((dbo_MyTab2.MyCol4)=False)));

    Given that in the backend database:
    Code:
    	MyTab1.MyCol1 is (bit, not null)
    	MyTab2.MyCol4 is (Flag(bit), not null)
    The problem is that when I execute the query, it prompts a dialog box asking for the value of MyTab1.MyCol1.
    There should be none of this dialog pop-up. Anyone encountered this before?

    Hope this helps make the problem clearer?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What is the BE database? WTF is Flag(bit)?

    There are problems sometimes of Access linked tables accessing SQL Server Bit columns, but I forget much of the detail. I think being NOT NULL and also adding a column of type TIMESTAMP are amongst the solutions.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2006
    Posts
    111

    BackEnd

    The backend database is SQL Server 2005.
    And in that database the following tables have columns with datataypes defined as:
    Code:
    	MyTab1.MyCol1 is (bit, not null)
    	MyTab2.MyCol4 is (Flag(bit), not null)
    Thanks for having a look at my question...

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Try adding a timestamp column to the table.

    Do you know what Flag(Bit) means? Could you run the below in SSMS?
    Code:
    SELECT    *
    FROM    sys.types
    WHERE    name LIKE '%bit%'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2006
    Posts
    111
    I'll run that script tomorrow, i've left office a few hours ago
    also i'm afraid i'd have to do the fix within the (frontend) access report query as i don't think at this stage i can make any changes to the database.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by g11DB
    i don't think at this stage i can make any changes to the database.
    I think you might be stuffed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ramblings from the bottom of a glass:

    the A-definition of False is 0 (bit-wise: 0000000000000000)
    the empirically apparent A-definition of True is Not False (contrast with the official definition which is bit-wise 111111111111111 (or long-wise -1)(which is a bit-wise Not 0))
    in english 1, 2, 3, 4, and -1 are all True (Not False)

    meanwhile - i'm not sure you can get there with Like

    you might get a result if you try:
    = False 'if you are looking for False of course
    or if you are looking for True:
    someField <> False
    or
    Not someField = False

    personally, i would run this via a passthru query and 'manually' (??? is that the correct word for something done in code ???) 'translate' True (A-long -1) : 1 (leaving SQLserver to interpret the '1' as Bit) similarly translating False : 0

    izy
    currently using SS 2008R2

Posting Permissions

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