Bdtjtk

I'm trying to create a query that will accept input from a form (from a set of 3 radio buttons, one for "male" one for "female" and one for "either") and return all the entries of the specified input.

I'm a bit of a newbie to MS Access so apologies if this is a super basic question. I've looked a lot online to find a solution but can't find one that works!

The query I have so far is this:

   IIf([Forms]![AgeRangeQueryForm]![GenderOptionGroup]. 

   [Value]=1,"M",IIf([Forms]![AgeRangeQueryForm]![GenderOptionGroup]. 

   [Value]=2,"F",IIf([Forms]![AgeRangeQueryForm]![GenderOptionGroup]. 

   [Value]=3,([Child Details].[Gender])="M" Or ([Child Details]. 

   [Gender])="F")))

If it helps the SQL code for the query is this:

   WHERE ((([Child Details].Gender)=IIf([Forms]![AgeRangeQueryForm]! 

    [GenderOptionGroup].[Value]=1,"M",IIf([Forms]![AgeRangeQueryForm]! 

    [GenderOptionGroup].[Value]=2,"F",IIf([Forms]![AgeRangeQueryForm]! 

    [GenderOptionGroup].[Value]=3,([Child Details].[Gender])="M" Or ([Child 

    Details].[Gender])="F")))) 

I can make this work for when Male and Female are selected, but unfortunately I can't get it to work if I select either. In this case it should be just returning all entries regardless of what gender is in the field but it just returns nothing. Any pointers would be appreciated!

Thanks!

The issue with your code is that if the final option is selected, the resulting WHERE clause would read:

where [Child Details].Gender = ([Child Details].[Gender]="M" or [Child Details].[Gender]="F")

And since [Child Details].Gender is string-valued, it will never equal the boolean value returned by the bracketed expression.

There are multiple ways to achieve this: one possible method is using a combination of the like operator and the choose function:

where 

    [child details].gender like 

    choose([Forms]![AgeRangeQueryForm]![GenderOptionGroup],"M","F","[MF]")

Alternatively, you could use use the switch function in a similar way:

where 

    [child details].gender like 

    switch

    (

        [Forms]![AgeRangeQueryForm]![GenderOptionGroup]=1,"M",

        [Forms]![AgeRangeQueryForm]![GenderOptionGroup]=2,"F",

        [Forms]![AgeRangeQueryForm]![GenderOptionGroup]=3,"[MF]"

    )

Or, just using boolean logic:

where 

([Forms]![AgeRangeQueryForm]![GenderOptionGroup] in (1,3) and [child details].gender = "M") or

([Forms]![AgeRangeQueryForm]![GenderOptionGroup] in (2,3) and [child details].gender = "F")

Or, if you don't wish to restrict the results returned when the third option is selected, precede all of the above filters with:

([Forms]![AgeRangeQueryForm]![GenderOptionGroup] = 3) or

For example:

where 

    ([Forms]![AgeRangeQueryForm]![GenderOptionGroup] = 3) or

    ([child details].gender = choose([Forms]![AgeRangeQueryForm]![GenderOptionGroup],"M","F"))