Querying a true/false field so a null returns all records

[securitywyrm]

Let's say I have a table of two columns, "Contact Name" (Short text) and "Wears Glasses" (True/false).

I have a query to generate a list of contacts, and a dropdown where you pick either "Has glasses" or "doesn't have glasses", their bound columns reading -1 (True) and 0 (false) respectively. However, I want to have a third option, "Show all" and since that would be a null, it returns no records.



So my question is: How do I set a query that searches a field to return ALL values for that field if there is a null value for criteria?

[CJ_London]

without any field, form or control names, you will need to adapt this

WHERE myfield=forms!myform!mycontrol OR forms!myform!mycontrol is null

[Micron]

How do I set a query that searches a field to return ALL values for that field if there is a null value for criteria?

AFAIK,
- a yes/no field cannot contain Null (probably the reason for the suggestion).
- null can be the default field value, but Access will convert Null to 0 without telling you. The triple state of a checkbox may be used to handle null, but I believe that has more to do with the control value rather than the field it is bound to. I suspect this is because checkboxes can be used in option groups or as stand alone checks; either of which means they're unbound.

Number fields are often used instead because they will accept genuine Nulls
see http://allenbrowne.com/NoYesNo.html

[securitywyrm]

I mean if the search criteria is null. So the query says "Show me the field for does this person have glasses" and the criteria points to a field that is blank. Currently it returns no value. I've tried the following

1. A * (wildcard), and Like "*", hoping that a wildcard would pull back all values. However I think it's treating it literally, so it's searching a true/false value for that text. Is there a way to make a query treat the field as a literal instead of value?
2. "0 or -1" again, it's treating it as text instead of a command where you'd normally put it in a query.

So my question is: How can I get a query to treat a form field as if it was typed directly into the query?

[Micron]

Is there a way to make a query treat the field as a literal instead of value?

Not sure you're expressing yourself correctly here. A literal would be WHERE Pet = "Dog". In query design view, this would be the word "Dog" in the Pet field. Dog (the criteria) is the literal.

How can I get a query to treat a form field as if it was typed directly into the query?

Suggest you always post what you tried. "I tried 'Like' " doesn't mean your syntax was correct. Note: LIKE can only be used with text, not numerical values.

There are several ways to take care of this. I suspect you're more interested in the fastest fix. If we're talking text values in the control, try
LIKE "*" & Forms!YourFormName.YourControlName & "*" . If the control contains nothing, all records should be returned.

The alternatives are:
- validating that controls needed for a query are not empty (null) or don't contain numbers where text is required (or vice versa)
- building sql in vba. The main part (constant) is assigned to a string variable (strFirstPart, or whatever). The WHERE part is assigned to strWhere, but the code for this tests controls for values and if not found, excludes them from the WHERE string. Any other volatile parts are assigned to other string variables, such as ORDER BY in strOrder. Somewhere along the line, the whole thing gets strung together.

"0 or -1" again, it's treating it as text instead of a command where you'd normally put it in a query.

If there are issues where your query treats a number as text or vice versa, then it is a problem of using quotes where you should not, or not where you should. Any value wrapped in " " or ' ' is treated as text. It's one of the more confounding tasks for newcomers to properly concatenate sql parts when using control references instead of values when creating sql in code. Not sure if you're doing that or not. Regardless, as previously noted, these values don't make for good data types where Nulls are allowed. Add to that the oddity that while 0 is always False in Access, sometimes True is 1, sometimes it's -1. I think they're best avoided unless the field cannot except null.

[securitywyrm]

AFAIK,
- a yes/no field cannot contain Null (probably the reason for the suggestion).
- null can be the default field value, but Access will convert Null to 0 without telling you. The triple state of a checkbox may be used to handle null, but I believe that has more to do with the control value rather than the field it is bound to. I suspect this is because checkboxes can be used in option groups or as stand alone checks; either of which means they're unbound.

Number fields are often used instead because they will accept genuine Nulls
see http://allenbrowne.com/NoYesNo.html

I tested it and it worked. Now here's where it gets tricky: There's currently three fields like that, where I want it to show all records if nothing was selected, and there may be more in the future. Will I be able to just keep adding "Or is null" to each field criteria?

[ArviLaanemets]

Have the dropdown value integer, values for dropdown 1, 2 and 3, and selections displayed as "Has glasses", "Doesn't have glasses" and "Show All"

The event composing the query string must check for dropdown value, and generates the string according to it.

In case several dropdowns, you start composing the query string based on 1st dropdown, then you add conditions based on 2nd dropdown (taking into account previous dropdown), etc.

[CJ_London]

Note: LIKE can only be used with text, not numerical values

actually, not true, you can

numfield like "*123*"

will return

12345
6123
40.123

[Micron]

I stand humbly corrected. Another false memory - have a lot of those now, it seems.