Query with 2 values?

  • Thread starter Thread starter Jen
  • Start date Start date
J

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.
 
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])
 
Hi Ken. Tried that earlier, but then the query returns "1 Or 2" as a string
or text, won't work.

Jen

Ken Snell said:
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
Click to expand...
2";[Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status])
 
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
Click to expand...

_____________
Duane Hookom
MS Access MVP


Jen said:
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.
Click to expand...
 
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.
 
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).
 
Thank you Duane, works perfectly. Don't fully understand why yet but I will
study "Instr" a little closer now. Jen.

Duane Hookom said:
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
Click to expand...

_____________
Duane Hookom
MS Access MVP
Click to expand...
 
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


Jen said:
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).

PC Datasheet said:
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.
Click to expand...
Click to expand...
 
Very neat, Duane!

--
Ken Snell
<MS ACCESS MVP>

Duane Hookom said:
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
Click to expand...

_____________
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:
Click to expand...
IIf([Forms]![startform]![subform_lista_per_tid_status].[Form]![frame_status]
=5;1 Or
Click to expand...
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.
Click to expand...
Click to expand...
 
Back
Top