Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Unanswered: really weird query problem (-1 vs true)

    I'm using MS Access 2003 and SQL Server 2000 for an application I've been running for a few years.

    I've got a query which filters out the non-active employees by placing a -1 in the criteria box for the ysnActive field. Here's the kicker:

    • I was seeing my Active Employees Friday, but Monday the query returns nothing - there were no changes to the application.
    • I verified that the data still shows active employees - it does.
    • If I change the MS Access query to filter with 1 instead of -1, I see my Active employees. (This is NOT a Pass-Thru query.)
    • If I change the MS Access query to filter with TRUE instead of -1, I see my Active employees.
    • If I restore the SQL Server database to Friday morning and run the same application - the query works with -1 or True.
    • I used SQL Compare (Red Gate) to compare the database schema - they seem to be identical.
    • I tried rebuilding the indexes - this did not help.


    I'm stumped. I have no clue where to look next. Any guidance would be greatly appreciated.

    Thanx!

    Angelo

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so presumably the problem is that you are relying on a sepcific numeric value rather than the true/false intrinsic constants.

    you cannot guarantee that the numeric representation is the same between databases and different versions of databases. for a true / false value use the constants true or false

    my suspiscion is that a change to was applied to the database engine or drivers, or possibly compatabiity mode has been turned off in Access
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2009
    Posts
    4
    so presumably the problem is that you are relying on a sepcific numeric value rather than the true/false intrinsic constants. You are correct! I won't do that again. I will try to find all occurences and change them.

    you cannot guarantee that the numeric representation is the same between databases and different versions of databases. for a true / false value use the constants true or false Never crossed my mind that Access would every change they way they referred to boolean values - but still, it is True/False from now on.

    my suspiscion is that a change to was applied to the database engine or drivers, or possibly compatabiity mode has been turned off in Access That's what's mind boggling. Right now, using my ONE Access application, I link to testDB and it works. I then restore testdb from two days ago - and it does not work. All of this with:
    • one version of Access
    • one copy of the Access Application
    • one version of SQL Server.
    • The only difference is which backup of testDB I restore to testDB.
    • It seems like it has to something different in one backup than it is in the other - because everything else is the same.
    • The more I think about it, the more confused I get!

    Thanx!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its a corporate installtion it possibel the network trolls applied a patch which has caused a change

    if its a private or SME installation these days most people have autoupdate switched on so you may have had a patch applied without realising

    however I suspect the more likely culprit is a setting has been inadvertantly flipped within Access, or possibly within SQL Server.

    there are good reasons to stick to ANSI SQL wherever possible and not use dodges or workarounds.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2009
    Posts
    4
    I've trimmed my problem down to SQL Server - I think!

    If I have a query in SQL SERVER -
    Select * from employee where bitActive = -1

    DB1 is a backup of my database from Friday morning.
    DB2 is a backup of my database from Saturday morning.

    If I point the qry to DB1, it returns all Active Employees
    If I point the qry to DB2, it returns nothing.

    I'd NEVER compare a bit to -1, but when it works - it works when called thru access. When it doesn't - access returnes nothing. And in Access, you would booActive = -1 has always worked in the past (even though intrinsic value comparisons are preferred).

    So - what could have happened Friday to cause this?????
    Thanx again!
    Confused as ever!

  6. #6
    Join Date
    Jul 2009
    Posts
    4
    In case I didn't mention it - in sql server bitActive=-1, bitActive=1 and bitActive=True use to all work.
    Now bitActive=1 and bitActive=True work, but bitActive=-1 does not work.

    I never tried to see if bitActive=-1 in SQLServer before.
    Last edited by Angelo Programmer; 07-21-09 at 13:52.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Make sure to "Refresh" your linked tables (or even delete them from MSAccess and re-link them) - in ALL mdb versions floating around. It could be a simple matter where the tables aren't refreshed which should be done every time you make a SQL Server change (again, in all interfaces).

    Also make sure that in your query, you highlight the column and format it properly (or use an expression to format it ie. TF: format([MyField],"Yes/No") and then use the appropriate criteria.

    In regards to SQL Server, it treats true/false values as 1,0 versus MSAccess treating it as -1,0. It should be a bit field on SQL Server with the default (0). Don't enter -1 values directly into SQL Server. I believe it is the ODBC DSN which translates it to a -1 though for MSAccess code to continue working. Still, I'll usually format my field in the query to make sure. If you start to use code such as stored procedures or views, you'll then use syntax such as 1 or % versus -1 or * in the stored procedure (on SQL Server.) Otherwise in MSAccess, the bit is treated as Yes/No.
    Last edited by pkstormy; 07-22-09 at 02:46.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Angelo Programmer
    I've trimmed my problem down to SQL Server - I think!

    If I have a query in SQL SERVER -
    Select * from employee where bitActive = -1

    DB1 is a backup of my database from Friday morning.
    DB2 is a backup of my database from Saturday morning.

    If I point the qry to DB1, it returns all Active Employees
    If I point the qry to DB2, it returns nothing.

    I'd NEVER compare a bit to -1, but when it works - it works when called thru access. When it doesn't - access returnes nothing. And in Access, you would booActive = -1 has always worked in the past (even though intrinsic value comparisons are preferred).

    So - what could have happened Friday to cause this?????
    Thanx again!
    Confused as ever!

    ..honestly, what called this?
    A: bad query design
    had you used the intrinsic constants you would not have had the problem
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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