Keep in mind that Access true/false columns are NOT -1 and 0.
I also STRONGLY suggest you don't cast the value using abs() as that would convert the value form true/false into a interger.
You also don't want to use some kluge since there might be 100's of other places in your application where this could become an issue and as a result cause MUCH pain and suffering here.
So keep in mind that while SQL server does in fact using a VERY small integer column (1 bit!!!), it is not a true/false column but simply only a integer column.
In access, we DO USE a real true/false. And in our quires we use
Where ActiveInvoice = True
We as a general rule do NOT use -1 or 0 in our code nor our quires. (at least I hope we don't, since this suggests one is casting a integer value of -1 or 0 into true/false.
The MOST important concept here is the ODBC driver will convert the bit (integer) into a CORRECT Access True/false column. (assuming the data was up-sized correctly in the first place).
So the first thing to check is to ensure that the Access linked table sees this column as true/false. Open up the linked table in design view FROM Access (ignore the message about this being a linked table).
Check (and then check again) to ensure that the column in question is defined as a Yes/No column. If it is a integer or some such then I STRONGLY suggest you had a issue during the up-sizing and I would convert this to a bit column, and then re-link the
Access client.
And while the linked tabled DOES show -1 and 0, you will in fact see that ALL REGULAR Access tables will do the same ****IF**** you REMOVE the format in the non linked table.
So in table desing (linked or not) you can check the "lookup" setting and it will be set to plain text box or check box (in fact this how you change the default column from showing a check box as opposed to a text box with -1/0). Note also the format option
to choose on/off or yes/no or true/false. If you remove this setting – you get -1, 0 (and do so even with local tables).
So if you remove the formatting in Access (all cases linked or not) then we see -1 or 0 but you STILL WANT to use True and False in your querires. Best way to think of this just like interal date format vs external – Access has a internal Boolean format
and it is NOT -1 and 0 but true/false.
So Access linked tables to SQL server are NOT any different. The only real surprise is that a linked table by default has NO formatting set and the display "check box" is turned off.
In fact you CAN open up the linked table in design view and again ignoring the linked table message in fact add format to that column and save it (and even change the plain text box in lookup to check box). The result will be a table that display JUST
like any regular non-linked Access table.
However, you don't need nor should have to change the format as per above.
The base issue here is your check boxes on the form are likely un-bound (or bound to a wrong data type column that was messed up during the up-size.
The fix here is to CAST the check box into a REAL AND CORRECT Boolean value.
This should work:
= cbool([Forms]![fmIE's]![ckCompleteProjects])
Note how we force the check box to return a true/false value.
So cast (convert) the check box into a true false (and ALSO remember to set the default for this check box when the form loads to False. (again don't use 0, but false).
You REALLY want to make sure you have this correct else in a large up-size project you will have a huge mess and can introduce 100's of bugs that you will chase down for a long time.
It is possible that the original table in Access was not a yes/no and was a integer – I would check this out, but if that was the case then case the check box using cint( ) as opposed to cbool()
So "cast" the check boxes into a correct true/false and this should work.
And in your query always use True/False – even if no formatting you will see a (-1/0) but you might see "true/false" or "on/off" or "yes/no" depending on the format setting, but in all cases we still shold use true/false just like we use USA format for dates.
Best regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
checkbox query error when migrating backend from Access to SQL
