Parameter query - allow user to select all

[Astello]

I want to be able to have multiple parameter queries, but the user needs to have the option of entering a parameter or not limiting the output at all. How do I let the user choose "all", or what do they type in so that nothing is excluded?

 

[neileg]

Use the Like comparator, eg:

Like [MyTextBox] & "*"

If MyTextBox is Null, this becomes Like * which returns all records.

 

[Brianwarnock]

Not to be a party pooper but how does this cope with the situation where the user can select say ANN or ANNE, if the user is inputting as opposed to selecting from a list then Like [MyTextbox] works with the user entering * when he wants all.

Brian

 

[neileg]

Mmm... Don't understand the question, Brian.

The Like solution is not perfect. Like ANN* will match ANNE or ANNETTE.

Entering * in the text box won't return all the records for a normal parameter query. You'd have to build the SQL in text.

 

[Brianwarnock]

Hmm, just tried it, Criteria Like [mybox] when the pop asked for parameter in mybox entered * and got all records.

Brian

 

[Astello]

Thanks so much you guys! In this query I'm pulling from limited choices, so there won't be a discrepancy like with Anne and Annette.

Did you know my name is Anne, or was that just a coincidence...

 

[Brianwarnock]

It was the simplest example I could think of, perhaps helped by the fact that I play bridge against Ann +Anne

Brian

 

[Astello]

That's pretty funny Is there a way to modify this so the user can enter one thing, multiple things, or * to get all, without putting a constraint on the number of entries?

 

[Brianwarnock]

I suspect the answer is no if I understand your question.
Lets stick with names and assume that the user is happy with the * wildcard

Then using Like[entrytext] allows you select Ann Anne etc by entering Ann* or all names beginning A with A* or containing A with *A* or all with just *, but I can think of no way to select Ann and Brian etc with the number of selections being open ended.

Jon K has a sample database dealing with criteria in the sample db area but I dom't know whether it answers your question.

Brian

 

[Astello]

I was hoping there was some way to type in multiple constraints, like an AND statement or something. But so far I've found nothing of the sort, which makes sense. Thanks again for all your help.

 

[gemma-the-husky]

its hard to have too many such all or one choices, as the underlying query becomes too complex. But you can give your users a combo box to select their choice. Design the combo box as a normal access query and then union it with one further line, where the key is "-1" (assuming your combo box returns a numeric key and the column text is "<ALL>". Using the < character sorts the ALL at the TOP of the combo box.

Your query now needs two criteria rows. One row is if the target choice equals the combo box value. The or row is if the combo box value was -1. (You will probably need to test these with functions). Because you now need two rows to test one combo box, you would need four rows for 2 combo boxes, and 8 for three etc, which is why it becomes impratical to have more than a couple.

I hope this makes sense

 

[Brianwarnock]

I was hoping there was some way to type in multiple constraints, like an AND statement or something. But so far I've found nothing of the sort, which makes sense. Thanks again for all your help.

You cannot pass commands through to the criteria only parameters. If nulls would not be an issue you could design a form with loads of text boxes on and right criteria to access them only entering as many as required, but it would have a finite number not be open ended.

I have attached an example of what I mean

Brian

 

[Astello]

I will work on both of these and see what I come up with. Thanks so much!

 

[Astello]

By the way, the WHERE statement I came up with for selecting one param or all is this code:

WHERE
((([DataTable].[Division Name])=IIf([Division Name?] Is Null Or [Division Name?]="all",[Division Name],[Division Name?]))
AND (([DataTable].Type)=IIf([Type?] Is Null Or [Type?]="all",[Type],[Type?]))
AND (([DataTable].[Dest ST])=IIf([Destination State?] Is Null Or [Destination State?]="all",[Dest ST],[Destination State?]))
AND (([DataTable].[Dest Zip])=IIf([Destination Zip?] Is Null Or [Destination Zip?]="all",[Dest Zip],[Destination Zip?])));

I used it to select the division, type of transportation, destination city and destination zip from a database with shipping information. This lets the user type in a constraint, or to enter "all" or leave it blank to not restrict the field. For anyone else with this question.

 

[EMP]

By the way, the WHERE statement I came up with for selecting one param or all is this code:

WHERE
((([DataTable].[Division Name])=IIf([Division Name?] Is Null Or [Division Name?]="all",[Division Name],[Division Name?]))
AND (([DataTable].Type)=IIf([Type?] Is Null Or [Type?]="all",[Type],[Type?]))
AND (([DataTable].[Dest ST])=IIf([Destination State?] Is Null Or [Destination State?]="all",[Dest ST],[Destination State?]))
AND (([DataTable].[Dest Zip])=IIf([Destination Zip?] Is Null Or [Destination Zip?]="all",[Dest Zip],[Destination Zip?])));

I used it to select the division, type of transportation, destination city and destination zip from a database with shipping information. This lets the user type in a constraint, or to enter "all" or leave it blank to not restrict the field. For anyone else with this question.

Thanks for posting the criteria you used. But there is a limitation in your criteria. In

[Division Name] = IIf([Division Name?] Is Null Or [Division Name?]="all", [Division Name], [Division Name?])

when the condition is true, [Division Name]=[Division Name] will leave out all the Null values in Division Name.

So the criteria work only if the fields used in the criteria do not contain Null values.

^

 

[Astello]

In the database I'm running, a record with a null value for anything I am pulling would be incomplete and would need to be cut out anyway. This query takes care of that for me. But I see what you're saying.

 

[EMP]

In the database I'm running, a record with a null value for anything I am pulling would be incomplete and would need to be cut out anyway. This query takes care of that for me.

I'm glad it works for you.

I wrote to point out what I saw because the title of the thread says "Parameter query - allow user to select all"

^