Microsoft Access Query Should Return True or True and False, Only Returns True

Microsoft Access Query Should return true or true and false, only returns true

Operators (=, >, LIKE, etc) cannot be dynamic. The operator must be static, then IIf() returns parameter.

Could do something like:

WHERE fieldname LIKE IIf([Type True or All] = "True", -1, "*")

Or for less typing by user:

WHERE fieldname LIKE IIf([Type "T" for True or "A" for All] = "T", -1, "*").

Or this:

WHERE fieldname LIKE Choose([Type 1 for True, 2 for False, 3 for All], -1, 0, "*")

Or for text field:

WHERE fieldname LIKE IIf([enter value or ALL]="ALL", "*", [enter value or ALL])

I don't use dynamic parameterized queries - I prefer VBA to build criteria and apply to form or report. Especially advise not to use popup inputs because they cannot be validated, inputs on form can.

Why is MS access boolean true -1 rather than 1 or true?

I can't seem to find the exact source this is from, but I remember reading about this a while ago on I think MSDN. This answer has a technical description of Visual Basic's boolean true, which applies to Access as well.

If I remember correctly, it's because -1 is represented in binary with every bit set to 1 (1111 1111), while +1 only has the least significant bit set to 1 with all the rest 0 (0000 0001). Because false is represented as 0 (0000 0000), it's very easy to change between true and false using a bitwise NOT, but if true was anything else, a bitwise NOT would result in something that isn't false. Also, using bitwise AND to check for truth on any truthy value would work, while if true was 0000 0001 it would not.

MSACCESS Is there a way for a query with a Boolean field to echo the string representation of the Boolean without taking way it's boolean type?

You can set the Format property to True/False for your query's boolean field expression.

True/False is not included in the choices available in the Format drop down box, but you can type it in.

Applying that format to both field expressions in this query ...

SELECT (1=1) AS boolean_True, (1=2) AS boolean_False
FROM Dual;

... gives me this result in Datasheet View:

boolean_True boolean_False
------------ -------------
        True         False

The underlying values are still Access booleans (integer -1 or 0). Just the presentation of those values is changed.

Access Search Query Criteria : IF false return all records

Consider:

Nz(DepartureDate, 0) LIKE IIf(Forms!OrderSearchMenu!DisplaySearchDate Is Null, "*", Forms!OrderSearchMenu!DisplaySearchDate)

For more info review Microsoft Access Query Should return true or true and false, only returns true and when unknown in combobox is selected show all null values

How to correctly make a query design criteria with multiple parameters?

Using numeric criteria with choices like 2, 20, 21 means cannot concatenate with wildcard so 20 and 21 are not retrieved when 2 is selected.

Using a popup input prompt with that long statement in query will look messy.

LIKE IIf([Enter Zone (1, 2, 3, 4, 5, 20, 21, P, A, F, C, T, B, E, S) or Hit Enter for All] <> "", [Enter Zone (1, 2, 3, 4, 5, 20, 21, P, A, F, C, T, B, E, S) or Hit Enter for All], "*")

Using a reference to form control a little less messy:

LIKE IIf(NOT [Forms!formname!controlname] IS NULL, [Forms!formname!controlname], "*")

Either will allow returning records matching value entered or returning all if nothing entered. This will not solve the issue of excluding 20 and 21 from all - for that I recommend using a VBA solution that builds filter criteria and applies to form or report.

MS Access query to toggle between records with true and all records

Add the value of your control as a column inside the query:

FilterControl1: [Forms]![myForm]![chkActive]

and then set the criteria for this column to In ([Active], false).

The expression is true if either the control and the column match or if false is selected. false works as the all selector.

This may look a bit cryptical, but it also works well for other data types. Say you have a combobox with IDs and added ID = -1, Text ='<all>' with a UNION in the query.

Then ColumnN: [ControlName], Criteria: IN ([column], -1) works alike to filter either the selection or -1 for all.

I can also confirm that the filter does not work on loading. I guess that's just Access. A Me.Requery in Form_Load seems to work ok.

SQL: Return true if list of records exists?

Given your updated question, these are the simplest forms:

If ProductID is unique you want

SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)

and then check that result against 3, the number of products you're querying (this last part can be done in SQL, but it may be easier to do it in C# unless you're doing even more in SQL).

If ProductID is not unique it is

SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100)

---

When the question was thought to require returning rows when all ProductIds are present and none otherwise:

SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100))=3)

or

SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100))=3)

if you actually intend to do something with the results. Otherwise the simple SELECT 1 WHERE (SELECT ...)=3 will do as other answers have stated or implied.

---

Related Topics