looking for multiple values in a table

[Geoff]

I have a table with many records with a field that indicates a catagory
to which the record belongs. There are only two catagories. I have
assigned the text value of 1 or 2 in the field of the table to
distinguish the catagory.

I can run a query to pick all records in catagory 1 or catagory 2, but
i cannot seem to get the query running to show both catagories.

The criteria in my query is

IIf([Forms]![formname]![catagory]=1,"1",IIf([Forms]![formname]![catagory]=2,"2","*")).

I am using * as a wild card but i get no records returned when the
query runs. I have also used # in place of the * to no avail.

Any ideas greatfully received.

TIA.

Geoff

It must be something simple

 

[Allen Browne]

Switch the query to SQL View (View menu, in query design.)

Locate the WHERE clause. It will look like this:
WHERE [SomeField] = IIf([Forms]![formname]![catagory]=1,"1",
IIf([Forms]![formname]![catagory]=2,"2","*"))

Change it to look like this:
WHERE (([Forms]![formname]![catagory] Is Null)
OR ([SomeField] = [Forms]![formname]![catagory]))

If you actually have other boxes on the form that are involved in the
criteria, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The article explains how build a much more efficient filter string that uses
only those boxes where the user entered something. It also explains how to
do it in the query if you prefer, and the traps associated with that
approach.

 

[Geoff]

Allen

Thanks for this (from one Aussie to another)

Perhaps i should have been clearer.

The form has three option buttons in a frame of which the use selects
just one. The first option is to return catagory 1 records, the second
option button is to return catagory 2 records and the third option is
to return both. There are other criteria on the form to further filter
the records . The option value assigned to each button is 1, 2 and 3.
So when the user selects "Both" the value is 3 and the query returns no
records since the records only have a value in that field of 1 or 2.

Your code is great is the third option is a Null.

do i amend the code (if so to what?) or is there a fundamental problem
with the way i am trying to filter the records?

Thanks

Geoff

Switch the query to SQL View (View menu, in query design.)

Locate the WHERE clause. It will look like this:
WHERE [SomeField] = IIf([Forms]![formname]![catagory]=1,"1",
IIf([Forms]![formname]![catagory]=2,"2","*"))

Change it to look like this:
WHERE (([Forms]![formname]![catagory] Is Null)
OR ([SomeField] = [Forms]![formname]![catagory]))

If you actually have other boxes on the form that are involved in the
criteria, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The article explains how build a much more efficient filter string that uses
only those boxes where the user entered something. It also explains how to
do it in the query if you prefer, and the traps associated with that
approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table with many records with a field that indicates a catagory
to which the record belongs. There are only two catagories. I have
assigned the text value of 1 or 2 in the field of the table to
distinguish the catagory.

I can run a query to pick all records in catagory 1 or catagory 2, but
i cannot seem to get the query running to show both catagories.

The criteria in my query is

IIf([Forms]![formname]![catagory]=1,"1",IIf([Forms]![formname]![catagory]=2,"2","*")).

I am using * as a wild card but i get no records returned when the
query runs. I have also used # in place of the * to no avail.

 

[Allen Browne]

Okay try:
WHERE (([Forms]![formname]![catagory] = 3)
OR ([SomeField] = [Forms]![formname]![catagory]))

The basic concept is that the WHERE clause is something that evaluates to
True or False.

If the option group has the value 3, the first part is True, and so the
WHERE returns True for all records.

If the option group has a different value, the first part is False, it it
only returns True where the field matches the value in the option group.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Thanks for this (from one Aussie to another)

Perhaps i should have been clearer.

The form has three option buttons in a frame of which the use selects
just one. The first option is to return catagory 1 records, the second
option button is to return catagory 2 records and the third option is
to return both. There are other criteria on the form to further filter
the records . The option value assigned to each button is 1, 2 and 3.
So when the user selects "Both" the value is 3 and the query returns no
records since the records only have a value in that field of 1 or 2.

Your code is great is the third option is a Null.

do i amend the code (if so to what?) or is there a fundamental problem
with the way i am trying to filter the records?

Switch the query to SQL View (View menu, in query design.)

Locate the WHERE clause. It will look like this:
WHERE [SomeField] = IIf([Forms]![formname]![catagory]=1,"1",
IIf([Forms]![formname]![catagory]=2,"2","*"))

Change it to look like this:
WHERE (([Forms]![formname]![catagory] Is Null)
OR ([SomeField] = [Forms]![formname]![catagory]))

If you actually have other boxes on the form that are involved in the
criteria, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The article explains how build a much more efficient filter string that
uses
only those boxes where the user entered something. It also explains how
to
do it in the query if you prefer, and the traps associated with that
approach.

I have a table with many records with a field that indicates a catagory
to which the record belongs. There are only two catagories. I have
assigned the text value of 1 or 2 in the field of the table to
distinguish the catagory.

I can run a query to pick all records in catagory 1 or catagory 2, but
i cannot seem to get the query running to show both catagories.

The criteria in my query is

IIf([Forms]![formname]![catagory]=1,"1",IIf([Forms]![formname]![catagory]=2,"2","*")).

I am using * as a wild card but i get no records returned when the
query runs. I have also used # in place of the * to no avail

 

[Jamie Collins]

Okay try:
WHERE (([Forms]![formname]![catagory] = 3)
OR ([SomeField] = [Forms]![formname]![catagory]))

Alternatively, where arg_category is the value from the control:

WHERE category =
IIF(arg_category IN (1, 2), arg_category, category)

Jamie.

--