Option to select specific or all

[WHYDO4399]

Hello.
I have the following question. I have a query where It will prompt the user to enter either a specific value in a field and it will give him all records for that specific value or also to have the option of entering "all" and it gives him all values / records.

For example lets say that the field stored "M" for male or "F" for females. When the prompt runs they can enter either "M" or "F" and it would give him just those values. However if they enter "ALL" in the prompt then it gives him both "M" and "F" since those are all the values of this field.
Thanks in advance.

 

[dhookom]

Is your query the record source of a form or report? If so, I would use the value entered into the control on your form in the WHERE CONDITION of the DoCmd.Open... If nothing is entered into the form control, don't add anything to the WHERE CONDITION.

Duane
Hook'D on Access
MS Access MVP

 

[WHYDO4399]

Not tied to a form or report. just doing it within the query

 

[PHV]

In the criteria cell of your field in the query grid:

=[Enter M,F or ALL] OR [Enter M,F or ALL]='ALL'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WHYDO4399]

Hi PHV....The But if the user enters 'All' in the prompt box when the query runs would it not looks for "all" and since those are not the values nothing would be returned?

Essentially when the user enters "All" the query should actually return M and F if they enter "All" I am using this just as an example as in the table I am using for this query there are actually many different values in the table that are available and sometimes we want to view all records and sometimes just some that meet the criteria entered

 

[PHV]

Did you even try my suggestion ?
If the user enters "All" then the criteria is always true ...

 

[WHYDO4399]

This worked PHV thanks for a million.