Criteria against checkbox only working on false?

[okerix]

I am having some issues creating a query and one columns has a criteria that runs against a forms unbound checkbox. I run a MS SQL backend and these particular columns have a datatype of bit. So they are True / False in the backend db. I have an access frontend and the linked table converts these columns to 0 or -1 as it should. So I have a form that users fill out to filter the query in the way they want to. One being a checkbox named [Tech]. If [Tech] is checked I want the query to bring back the the True/-1 values for this specific column or if not checked bring the False/0. If I hand type in True, Yes, or -1 they all filter as they should; same goes with False, No, and 0. So in the criteria of this column I have

IIf([Forms]![ReachQueryForm]![Tech],True,False)

This only works if the checkbox is not checked and then the query correctly pulls the False records back. It does pull any results when it is checked.
I have tryed
IIf([Forms]![ReachQueryForm]![Tech] = True,True,False)
and that didnt work at all. I have also interchanged the True and False with Yes, No, -1 and 0. I am out of ideas and could really use some help. Thanks for any thoughts on this.

 

[MSAccessRookie]

I am having some issues creating a query and one columns has a criteria that runs against a forms unbound checkbox. I run a MS SQL backend and these particular columns have a datatype of bit. So they are True / False in the backend db. I have an access frontend and the linked table converts these columns to 0 or -1 as it should. So I have a form that users fill out to filter the query in the way they want to. One being a checkbox named [Tech]. If [Tech] is checked I want the query to bring back the the True/-1 values for this specific column or if not checked bring the False/0. If I hand type in True, Yes, or -1 they all filter as they should; same goes with False, No, and 0. So in the criteria of this column I have

IIf([Forms]![ReachQueryForm]![Tech],True,False)

This only works if the checkbox is not checked and then the query correctly pulls the False records back. It does pull any results when it is checked.
I have tryed
IIf([Forms]![ReachQueryForm]![Tech] = True,True,False)
and that didnt work at all. I have also interchanged the True and False with Yes, No, -1 and 0. I am out of ideas and could really use some help. Thanks for any thoughts on this.

Did you try 1 instead of -1? Although MS Access and MS SQL Server both view False as 0, they each view True differently, with MS Access viewing it as -1, and MS SQL Server viewing it as 1. As a result, I have taken to checking for False first. That way, the True is always correct, becasue it is NOT False.

 

[okerix]

Changed it up and decided to use a combobox with Yes and No So if they select Yes it will filter by True's on the field and if the select there is no filtering done at all. I enter this in the criteria

IIf(([Forms]![ReachQueryForm]![Tech])="No",([dbo_ReachTrip].[Tech_Expert])<=2, True)

of course it only returns True statments and never returns the one false value. I have switched this IIf statement a million different ways with no luck.

 

[okerix]

Found my answer here

http://www.access-programmers.co.uk/forums/archive/index.php/t-141412.html

I never would of guessed just entering in the

.[column] name as the criteria to get all results back.