How to use an Option Group on a form as a criteria in a query ?

[MS]

Hello !

I have a form with a option group with 5 options.
I want to filter records with a priority field in this query.

The field have the value of 1,2,3,4 or 5.
I have tried IIF's VBA , but I cant figure this out !

The table should be filtered like this:
If I choose Option 1 I get all records with 1 in priority field.
If I choos Option 2 I get alle records with 1 or 2 in the priority field.
....
And last if I choose Option 5 I get all records.

Can anyone see what I'm missing

Leif Hole
(e-mail address removed)

 

[Duane Hookom]

I would write code like:

Dim strWhere as String
strWhere = "1=1 "
Select Case Nz(Me.OptGroup , 5)
Case 1 to 4
strWhere = strWhere & " And [Priority] = " & Me.OptGroup
Case Else
'do nothing
End Select
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

 

[Jeff Boyce]

In addition to Duane's approach, if the selection criterion will always be
priority field values less than or equal to the option field value, consider
a query that "looks" to the form's option field for a criterion. Perhaps
something like:

<=Forms!YourForm!YourOptionGroup

Good luck

Jeff Boyce
<Access MVP>

 

[MS]

I addition to this I need also to find records that have the values 1,2, 3,
and 1,2,3,4 in the priorty field.

Also I use the query in a list control on a form.

Leif Hole

I would write code like:

Dim strWhere as String
strWhere = "1=1 "
Select Case Nz(Me.OptGroup , 5)
Case 1 to 4
strWhere = strWhere & " And [Priority] = " & Me.OptGroup
Case Else
'do nothing
End Select
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
MS Access MVP
--

Hello !

I have a form with a option group with 5 options.
I want to filter records with a priority field in this query.

The field have the value of 1,2,3,4 or 5.
I have tried IIF's VBA , but I cant figure this out !

The table should be filtered like this:
If I choose Option 1 I get all records with 1 in priority field.
If I choos Option 2 I get alle records with 1 or 2 in the priority field.
...
And last if I choose Option 5 I get all records.

Can anyone see what I'm missing

Leif Hole

 

[MS]

I have tried that, also it wont work if I just put in >=3 in the criteria
for the query.
It might not work because its a number field ?
I will try to change it to a text field and see what happens.

Leif Hole