Query with 2 values?

[Jen]

In a qyery I retrieve a value from a form. The values comes from 5 Option
Buttons in a optionframe, values of the optionbuttons being 1, 2, 3, 4 and
5. If the value of the optionframe is 1, 2, 3 or 4 thats what the query
should stick with (and does too). But if the value is 5, then the query
should retrieve the values 1 and 2.

If I write "1 or 2" in the query criteria then I get these two. If I however
try to put them in an iif() statement in the criteria of the query like:

IIf([Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status]
=5;1 Or
2;[Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status])

then it doesn't work.

In summary, what I want to do is to have the query behave the same way as
when I write "1 or 2" (without "-marks) in the query's Criteria grid. This
only when the forms (from where I launch the query) optionbuttons frame's
value is 5. Otherwise the query should give whatever the optionframe's value
is (i.e 1, 2, 3, 4). Any ideas, or am I doing this wrong altogether?

Also tried different approaches with code within Vba, but without success.

Jen.

 

[Ken Snell]

Try putting " marks around the 1 Or 2. That might work.

IIf([Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status]
=5;"1 Or
2";[Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status])

 

[Jen]

Hi Ken. Tried that earlier, but then the query returns "1 Or 2" as a string
or text, won't work.

Jen

Try putting " marks around the 1 Or 2. That might work.

IIf([Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status]
=5;"1 Or

2";[Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status])

 

[Duane Hookom]

I would create a new column in the query grid
InWhere:
Instr(Choose([Forms]![startform]![subform_lista_per_tid_status].[Form]![fram
e_status], "1","2","3","4","12"), [Status])
and set the criteria to

0

_____________
Duane Hookom
MS Access MVP

In a qyery I retrieve a value from a form. The values comes from 5 Option
Buttons in a optionframe, values of the optionbuttons being 1, 2, 3, 4 and
5. If the value of the optionframe is 1, 2, 3 or 4 thats what the query
should stick with (and does too). But if the value is 5, then the query
should retrieve the values 1 and 2.

If I write "1 or 2" in the query criteria then I get these two. If I however
try to put them in an iif() statement in the criteria of the query like:

IIf([Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status]
=5;1 Or
2;[Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status])

then it doesn't work.

In summary, what I want to do is to have the query behave the same way as
when I write "1 or 2" (without "-marks) in the query's Criteria grid. This
only when the forms (from where I launch the query) optionbuttons frame's
value is 5. Otherwise the query should give whatever the optionframe's value
is (i.e 1, 2, 3, 4). Any ideas, or am I doing this wrong altogether?

Also tried different approaches with code within Vba, but without success.

Jen.

 

[PC Datasheet]

Jen,

Put the following expression in the criteria of your query:

Between 1 And
IIf([Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status]
=5;2,4)

If you check option button 5, the query will return the records where the query
field is 1 or 2 and if you check any other button, the query will return records
where the query field is 1, 2 ,3 or 4.

 

[Jen]

Thanks for your suggestion. Now I get 1 and 2 when choosing 5 on the form,
but instead of getting 1-4 on all the rest I should get only one value
(Option 1 selected = 1, option 2 selected = 2 and so on).

 

[Jen]

Thank you Duane, works perfectly. Don't fully understand why yet but I will
study "Instr" a little closer now. Jen.

I would create a new column in the query grid
InWhere:
Instr(Choose([Forms]![startform]![subform_lista_per_tid_status].[Form]![fram
e_status], "1","2","3","4","12"), [Status])
and set the criteria to

0

_____________
Duane Hookom
MS Access MVP

 

[PC Datasheet]

Create two hidden (not visible) textboxes on your form and name them Option1 and
Option2. Put the following code in the Afterupdate event of Frame_Status:

Select Case Me!Frame_Status
Case 1-4
Me!Option1 = Me!FrameStatus
Me!Option2 = Me!FrameStatus
Case 5
Me!Option1 = 1
Me!Option2 = 2
End Select
Me.Requery

Put the following expression in the criteria of your query:

Between [Forms]![startform]![subform_lista_per_tid_status].[Form]![Option1] AND
[Forms]![startform]![subform_lista_per_tid_status].[Form]![Option2]

WATCH the word wrap!


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com

Thanks for your suggestion. Now I get 1 and 2 when choosing 5 on the form,
but instead of getting 1-4 on all the rest I should get only one value
(Option 1 selected = 1, option 2 selected = 2 and so on).

Jen,

Put the following expression in the criteria of your query:

Between 1 And
IIf([Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status]
=5;2,4)

If you check option button 5, the query will return the records where the query
field is 1 or 2 and if you check any other button, the query will return records
where the query field is 1, 2 ,3 or 4.

 

[Ken Snell]

Very neat, Duane!

--
Ken Snell
<MS ACCESS MVP>

I would create a new column in the query grid
InWhere:
Instr(Choose([Forms]![startform]![subform_lista_per_tid_status].[Form]![fram
e_status], "1","2","3","4","12"), [Status])
and set the criteria to

0

_____________
Duane Hookom
MS Access MVP

In a qyery I retrieve a value from a form. The values comes from 5 Option
Buttons in a optionframe, values of the optionbuttons being 1, 2, 3, 4 and
5. If the value of the optionframe is 1, 2, 3 or 4 thats what the query
should stick with (and does too). But if the value is 5, then the query
should retrieve the values 1 and 2.

If I write "1 or 2" in the query criteria then I get these two. If I however
try to put them in an iif() statement in the criteria of the query like:

IIf([Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status]

=5;1 Or

2;[Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status])

then it doesn't work.

In summary, what I want to do is to have the query behave the same way as
when I write "1 or 2" (without "-marks) in the query's Criteria grid. This
only when the forms (from where I launch the query) optionbuttons frame's
value is 5. Otherwise the query should give whatever the optionframe's value
is (i.e 1, 2, 3, 4). Any ideas, or am I doing this wrong altogether?

Also tried different approaches with code within Vba, but without success.

Jen.