Selecting between one and all items in a parameter control

[DrDoIT]

Hello Forum,
kindly assist me solve the following problem.

I have a Parameter form which provides various parameters to my queries. The Parameter Form has a Combo box with a list of 50 health facilities which the user can select according to their needs.

There are instances when a user needs data from just one health facility and at times the data is needed for all the 50 health facilities in the district.

How do i address both needs without having to keep adding and deleting the criteria in the underlying queries?

I have added the 'if' condition below in the queries criteria so that if the user does not enter a facility in the combo box, it runs all facilities and if they enter a facility name then the data in the reports is limited to that health facility.

IIf(IsNull([Forms]![Parameter Form]![Combo27]),"",[Forms]![Parameter Form]![Combo27])

It does not work.

Is the expression or VBA code supposed to be entered in the Query or in the Parameter form and how do I make it work?

Kindly assist.

Thank you.

DrDoIT.

 

[bob fitz]

I'm not sure this is correct, but try:
[Forms]![Parameter Form]![Combo27] Or Is Null

 

[bob fitz]

Sorry, I think that should be:
[Forms]![Parameter Form]![Combo27] And Not Is Null

 

[bob fitz]

This works for me:
[Forms]![Parameter Form]![Combo27] Is Null Or [YourFieldName]=[Forms]![Parameter Form]![Combo27]

 

[Brianwarnock]

That's called creeping up on the answer, or trial and error

The final answer is the standard one , and many criteria like that ANDed together work for multi criterial searches.

But whatever you do always save the query in SQL. View never from the design grid


Brian

 

[DrDoIT]

Thanks Brian, Thanks Bob

This works perfect!

[Forms]![Parameter Form]![Combo27] Is Null Or [YourFieldName]=[Forms]![Parameter Form]![Combo27]

Thank you

DrDoIT

 

[rkl122]

An alternative I've read about (not tested) uses LIKE keyword:

Like Forms![parameter form]![combo27] & "*"


-Ron

 

[Brianwarnock]

Like should only be used for partial string searches as it can produce unexpected results, it will not return null fields , true only relevant if selecting the all condition, and there is doubt about it using indexes even when starting with a constant string.

Brian