Search Query - Empty fields problem

[urosm993]

I have a search form with many fields including date range, name, some number ranges etc.
Have one button to run a search query so it can select from table searched data.
But the problem I'm facing is how to INCLUDE all the records when I don't specify a number range in the field.
I tried isNull function, and isEmpty but for some reason it's not working...

Is there an efficient way to solve this. If I leave the field blank, to just don't effect the search in any way?


Thank you

[June7]

Do you mean if the control on form is left empty? You will have to provide the query with alternate value if user does not enter any. Is this a date parameter? Like:

BETWEEN Nz([Foms]![Search]![EFod], #1/1/1900#) AND Nz([Foms]![Search]![EFdo], #12/31/2900#)

If it is not a date but a number, then use some extreme numbers that will insure capture of records. And don't use the # delimiters.


I NEVER use dynamic parameterized queries. I prefer VBA: http://www.allenbrowne.com/ser-62.html

Re use of Nz() in query http://allenbrowne.com/QueryPerfIssue.html

[urosm993]

Thank you, it was helpful I have another thing to ask.
I have some check box fields in my table. For example is some person a smoker on not (yes/no), and I want to search it through my form with radio buttons like the picture i attached shows.
Can someone help mi with that, I see there are integer values attached to each radio buttons, but how could I write that in query...

[June7]

This is why I avoid Yes/No fields and never use dynamic parameterized queries. I really don't know any way to structure conditional parameter for Yes/No field to allow the All option.

[urosm993]

Well, as I'm trying to make extremely user-friendly database, I'm not really sure if just text field instead of check box is acceptable. There should be a way to show checked and unchecked when radio button "all" is selected. When I chose yes to select records with only checked, and when NO is chosen to select only record with unchecked boxes

[June7]

Maybe calculate another field and apply conditional to that constructed field.

SmokerYN: IIf([Smoker], "T", "F")

LIKE Switch([Foms]![Search]![option group]=1, "T", [Foms]![Search]![option group]=2, "F", [Foms]![Search]![option group]=3, "*")

Otherwise, I would use VBA.

[urosm993]

As I saw that there are numbers joined to these radio buttons, and I think that checkBox fields act in the way they have values 0 and -1. Not sure where I saw that, but i think 0 is unchecked and -1 is checked. And I could give -1 to Yes radio button and 0 to No radio button, and try to write a simple criteria Select whatever.. where [checkBoxField] =[Forms]![Search]![Radio1].. But I'm not really sure on syntax and If it's possible to solve it that way.

[June7]

No, that will not deal with the All selection. I think I have offered only viable approach for dynamic parameterized query.

[urosm993]

Well, I still have a button and I can run a query. You are saying that I cannot search for a checkBox value in the same way I do for text field values?

[June7]

Not if you want the 'All' option. They hold numeric data - wildcards are only useful with text data and the logical operators (=, <, >) won't help. Operators cannot be dynamic. That's why I suggest criteria on a calculated text value.

Let me double check on the wildcard.

Okay, I have learned something new. Wildcard works with yes/no field.

LIKE Switch([Foms]![Search]![option group]=1, "-1", [Foms]![Search]![option group]=2, "0", [Foms]![Search]![option group]=3, "*")

Set the Option Group with 3 as DefaultValue.

[urosm993]

Somehow it's not working. How could I search just records that are checked? Field IsSmoker Table People Criteria =-1 or true? I'm not sure how I can handle that in query design. Can I write that switch there? Under criteria I have to specify 'fields of what values will appear in the query'. Maybe =-1 AND [Forms]![Search]![option group]=1 OR =0 AND [Foms]![Search]![option group]=2 ....

[June7]

What does not working mean, what happens - error message, wrong results, nothing?

The suggested criteria worked in my db. What exactly did you do? Post the query SQL statement.

[urosm993]

Sorry I reply so late, I wasn't able to reply earlier. This is what I have done so far. This is a mess. It's not just what I wrote, access is adding something on it's own. I gave up on trying to add a code to SQL part, and trying to add to design. On these pictures is working search for all the fields, To show everything in search if either of them is empty.
Now it's not easy to add the option button search to all of that... Here are some parts to show you where I got confused adding what you have suggested






It's asking a value that should read from the OptionGroup. And If I enter 0 it shows all (as it should). All other numbers doesn't work (Shows nothing for 1 and 2)

[June7]

That popup normally means Access can't find the referenced object, usual cause is misspelling.

I did not suggest constructing the two right hand fields. Why do you have those?

The Switch expression goes in Criteria row below the Yes/No field. Just like any of the other parameters you already have.

However, as already noted, I would not use parameterized query. This query is getting too complex.

[urosm993]

Not sure about what right hand fields you are thinking about. With nulls?
Yes, it's asking for the value, and the funny part is, when I enter the value it still doesn't work. I got empty table, except for value 0, then I get all the records as I should