I have a query based report where a number of the fields in each record
are Yes/No data fields.
I would like this report to behave as follows:
If ALL of the Yes/No data fields in a given record have a value of YES,
I would like that particular record to be suppressed (I.e. NOT appear in
the report).
and
If ANY of the Yes/No data fields in a given record have a value of NO, I
would like that particular record to appear in the report.
How can this be done? 3 1495
Todd, the structure you describe usually represents something that has not
been normalized properly, i.e. you should create a related table with a
record for each choice that does apply instead of heaps of check boxes in
one record.
To work around this structure, you could use the fact that Access uses -1 of
True and 0 for False. Therefore summing the fields gives you a count of how
many are checked. Type the expression into the Field row of query design:
HowMany: Abs([Field1] + [Field2] + [Field3])
Then in the Criteria row, ask for fewer than the total number of boxes you
have:
< 3
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Todd D. Levy" <us************ **@spamex.com> wrote in message
news:MP******** *************** *@nyc.news.spea keasy.net... I have a query based report where a number of the fields in each record are Yes/No data fields.
I would like this report to behave as follows:
If ALL of the Yes/No data fields in a given record have a value of YES, I would like that particular record to be suppressed (I.e. NOT appear in the report).
and
If ANY of the Yes/No data fields in a given record have a value of NO, I would like that particular record to appear in the report.
How can this be done?
Allen,
Thanks for the help; I am going to use your work around solution because
changes to the structure of the database are not possible at this time.
One question: does using Abs in the field row of the query treat the -1
for Yes/True as 1 so that the sum of 3 fields is 3 and not -3?
I would like to do one other thing for this report:
On the report itself I would like a Yes/No field with a Yes value to be
displayed with GREEN text, and a Yes/No field with a NO value to be
displayed with RED text.
I assume this is done with VBA code; does it have to be done on a field
by field basis individually, or is it possible to address this issue for
all Yes/No fields with a single block of code?
In article <40************ ***********@fre enews.iinet.net .au>, Al*********@See Sig.Invalid says... Todd, the structure you describe usually represents something that has not been normalized properly, i.e. you should create a related table with a record for each choice that does apply instead of heaps of check boxes in one record.
To work around this structure, you could use the fact that Access uses -1 of True and 0 for False. Therefore summing the fields gives you a count of how many are checked. Type the expression into the Field row of query design: HowMany: Abs([Field1] + [Field2] + [Field3]) Then in the Criteria row, ask for fewer than the total number of boxes you have: < 3
Yes Abs() drops the sign, returning the absolute value.
If you represent the control with a text box on your report, you could use
Conditional Formatting to set its BackColor. In report design view, select
the text box, and choose Conditional Formatting from the Format menu.
Conditional Formatting is not available in Access 97 and older, but you can
still include the color in the Format property of the text box.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Todd D. Levy" <us************ **@spamex.com> wrote in message
news:MP******** *************** *@nyc.news.spea keasy.net... Thanks for the help; I am going to use your work around solution because changes to the structure of the database are not possible at this time.
One question: does using Abs in the field row of the query treat the -1 for Yes/True as 1 so that the sum of 3 fields is 3 and not -3?
I would like to do one other thing for this report:
On the report itself I would like a Yes/No field with a Yes value to be displayed with GREEN text, and a Yes/No field with a NO value to be displayed with RED text.
I assume this is done with VBA code; does it have to be done on a field by field basis individually, or is it possible to address this issue for all Yes/No fields with a single block of code?
In article <40************ ***********@fre enews.iinet.net .au>, Al*********@See Sig.Invalid says... Todd, the structure you describe usually represents something that has
not been normalized properly, i.e. you should create a related table with a record for each choice that does apply instead of heaps of check boxes
in one record.
To work around this structure, you could use the fact that Access
uses -1 of True and 0 for False. Therefore summing the fields gives you a count of
how many are checked. Type the expression into the Field row of query
design: HowMany: Abs([Field1] + [Field2] + [Field3]) Then in the Criteria row, ask for fewer than the total number of boxes
you have: < 3 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Robert Brown |
last post by:
suppose I have the following table:
CREATE TABLE (int level, color varchar, length int, width int, height
int)
It has the following rows
1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
|
by: Mark |
last post by:
Hi - I tried this in VS.Net, and also in the Web Matrix code below: -
but I am getting a type mismatch error.
The sql statement runs perfectly from within the Access Query Designer.
Can anyone verify the syntax, and that my .net code is ok - the type
mismatch is happening at: DataGrid1.DataSource =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Thanks in advance,
|
by: Steven D.Arnold |
last post by:
I have a query which does not use column indexes that it should use. I
have discovered some interesting behaviors of Postgres which may
indicate a bug in the database's query planning.
Take a look at the query below. There is a btree index on both
m.account_id and a.account_id. Query (1) does not use the index on the
messages table, instead opting for a full table scan, thus killing
performance. The messages table can contain...
|
by: carl.barrett |
last post by:
Hi,
I'm back with the same question as I still can't get it to display
my data the way I want it to.
The table lists information about a perpetrator involved with an anti
social behaviour order. The table contains personal information about
the individual as well as (here is where the letter dates comes in:)
e.g.
|
by: matt |
last post by:
hello,
i have an .RPT file that i am using to load a report via the ASP.NET
CrystalReportViewer control. i dont include my .RPTs as embedded
resources...instead i load the absolute path of the .rpt (on the file
system) to the control's ".ReportSource" property. i do this because i
like to move new versions of the reports w/o having to rebuild the
site.
then i pass in filter criteria to the control's ".SelectionFormula"
| |
by: lorirobn |
last post by:
Hi,
I have a report displaying items that are missing from a room. I
created 2 queries, the first getting the items IN the room, and the
second being an "unmatched" query that references the first query
where Item is Null. I use a subreport for the details, and the
results display correctly.
However, the Report_Details event of the subreport is executed about 2
or 3 times more than I would expect (I think 3 times when I have a
|
by: Phil Stanton |
last post by:
I am running a query that calls a function used to format addresses
depending on the width of a control on a report that shows that address. The
same query is used as the RecordSource of lots of similar reports, but all
with different sized Address text boxes. For the function to work, the
report need to be open in design view, so that the Text Box Width can be
"measured". The function is obviously called for each line of the query
(about...
|
by: dlite922 |
last post by:
I'm building a dynamic reporting system. The report can of course query multiple tables.
The query declares which tables it will access, the fields that it needs for display, and the dependent fieds or fields not in the report, but in the "filter" part.
The problem is when a "filter" is not used, the field will not show up in the dynamically built SQL.
For example, the user doesn't choose to see a report for a particular product,...
|
by: teddysnips |
last post by:
My clients have asked me to maintain a database that was developed in-
house. It's pretty good, considering the developer isn't a
"programmer".
The first thing they want me to do is to split it into a Front End/
Back End, which is very sensible. However, there are a number of ad
hoc queries that are required to be available to all users, which can
be edited, deleted or created "on the fly". The most sensible
solution would seem to be...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |