• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Show detail on report based on option group of a form

I have a report which uses a query as its record source.  But prior to the report being opened I have a form.  On the form is an option group with three choices.   One is "Active" which is not true in the query,. another is "Inactive" which is true in the query, and "All" in which case I want all records to be displayed in the report.  The reason I'm using a query is because there is another criteria where the user selects a customer from a combobox on the form.

So if the used selects "Active" (which is choice 1 in the option group), I want only the records which are false in the query to be displayed on the report.

But if the used selects "Inactive" (which is choice 2 in the option group), I want only the records which are true in the query to be displayed on the report.

And if the used selects "All" (which is choice 3 in the option group), I want all of the records to be displayed on the report.

The field in the underlying table is named "Inactive" and is a yes/no field.

I'm thinking I need a case statement in the detail section of the report but I do not know how to code this.

Can someone please help?

--Steve
0
SteveL13
Asked:
SteveL13
1 Solution
 
PatHartmanCommented:
Here's a query I use to populate a list box of clients.  It refers to an option group with three options.  1 = active, 2 = inactive, and 3 = all.  Notice it does two specific checks and then defaults to true as the third option.

SELECT tblClients.ClientID, [LastName] & ", " & [FirstName] AS FullName, IIf([forms]![frmClients]![fraStatus]=2,IIf([StatusID] = "I",True,False),IIf([forms]![frmClients]![fraStatus]=1,IIf([StatusID]= "A",True,False),True)) AS Status
FROM tblClients
WHERE IIf([forms]![frmClients]![fraStatus]=2,IIf([StatusID]= "I",True,False),IIf([forms]![frmClients]![fraStatus]=1,IIf([StatusID]= "A",True,False),True))=True
ORDER BY [LastName] & ", " & [FirstName];
0
 
SteveL13Author Commented:
Very helpful.  Thanks.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now