?
Solved

Show detail on report based on option group of a form

Posted on 2014-07-28
2
Medium Priority
?
335 Views
Last Modified: 2014-07-28
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
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40224890
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
 

Author Comment

by:SteveL13
ID: 40225114
Very helpful.  Thanks.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question