Search Form with Option group with 3 option buttons

[TenOc]

Please excuse me if I do not use the correct terminology, I’m a newbie


On my form I have an option group with 2 option buttons namely “Class A” and “Class B”. This form opens a query and later a report which allows me to select either class A or class B customers. I now want to add to this option group a third button namely, ”All” which will include both class A and class B customers in the report. The default values on the 2 option buttons are 1 and 2 respectively. The default value for the 3rd button I assume would be 3. In the customers table the stored value for Class is either 1 or 2, they are no number 3.




The current selection criteria in the query is “[Forms]![frmSearch]![opgClass]” -- all works well with 2 buttons. I have attempted, (when I have 3 buttons) to use a “like” criteria with wildcards with no joy. I need help with the new selection criteria in the query with 3 buttons. I think I may need some type of IIF statement but I do not know how to structure it if the option class is 3.

[June7]

LIKE IIf([Forms]![frmSearch]![opgClass] = 3, "*", [Forms]![frmSearch]![opgClass])

[Welshgasman]

Surely if it is All, you just do not use any criteria?

[June7]

My understanding is it's a dynamic parameter in query object. The parameter receives whatever is in option group. All values must be dealt with.

[Welshgasman]

My understanding is it's a dynamic parameter in query object. The parameter receives whatever is in option group. All values must be dealt with.

Ah, I see now.

[moke123]

Personally I like to use a select case statement with option groups. Easier to make changes and easier to follow.

Select Case YourOptionGroupName

Case 1
     strSql = "Select * ....... where SomeCriteria = Something

Case 2
     strSql = "Select * ....... where SomeCriteria = Something

Case 3
     strSql = "Select * ....... where SomeCriteria = Something

Case else
     msgbox "something went wrong"

End Select

The current selection criteria in the query is “[Forms]![frmSearch]![opgClass]”

Only use the option group values to indicate your choice. I would not use the OG values to associate to a value in your table ( like 1 for Class A, 2 for Class B)

[June7]

That VBA is not relevant to a dynamic parameterized query. However, need confirmation from OP if this is a DPQ.

I have never used dynamic parameterized queries. I prefer VBA to build filter criteria and apply to form or report.

[TenOc]

LIKE IIf([Forms]![frmSearch]![opgClass] = 3, "*", [Forms]![frmSearch]![opgClass])

Thanks this works. Problem solved