Solved

Show detail on report based on option group of a form

Posted on 2014-07-28
2
331 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 37

Accepted Solution

by:
PatHartman earned 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

737 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