I am using Access 2007 with a pass-through query. The data values that I am retrieving are from a bit column (0, 1) but when the pass-through query runs the data that is returned is either 0 or -1. I am calling a stored procedure on my SQL database for this pass-through query, this stored procedure works and returns the positive bit number of 1. Am I missing something here?
views:
276answers:
1
A:
This is just a guess, but true/false is frequently represented by -1/0 (more sufficiently as non-zero/zero, with -1 being a common choice for the non-zero) so is it possible a conversion to boolean is going on somewhere?
BarrettJ
2010-02-18 18:58:22
I am not explicity converting any columns. The stored procedure on the sql side is returning either a 0 or 1, but when I run the pass-through query in Access the returned values are 0 or -1.The code that I am using to run the pass through query is: exec sp_Excel_RIPSReview2;
mattgcon
2010-02-18 19:10:26
Is the data stored a Boolean value? If so, you test not for True/1 or False/0, but for Not False/False, since False is defined the same in all Boolean data types I'm aware of (i.e., as 0). This will make all code and SQL WHERE clauses compatible no matter how True is encoded.
David-W-Fenton
2010-02-18 21:47:43
Within SQL it is stored as bit (0, 1), the table that the pass-through is putting into, the same columns are set to Number, but the values become -1 for some reason.
mattgcon
2010-02-19 15:54:48
Access doesn't have a bit field type, it has only Yes/No which I'm guessing it is using a boolean internally, and while the physical bit is set to a 0 or 1, access when converting from a boolean to a number will use what is common to represent true as a number: -1. I would only worry about this if you were sometimes getting a -1 and sometimes getting a positive 1, the fact that it is consistently returning -1 is just indicating that Access is using -1 for true.
BarrettJ
2010-02-19 16:35:07