Shading records in a report - Kinda tricky

[Thomas Campitelli]

Howdy Folks,

I would like to shade some records in an Access report based on whether
the value for a given record is above a certain level. That's the basic
concern, but let me provide some more details.

My report is a summary of analytical chemistry data for groundwater. I
want to compare the results for various wells against governmental
groundwater standards. The format of the report is shown below:

Chemical Name Standard Result 1 Result 2 Result 3
Benzene 5 1,000 1,500 1,450
Arsenic 3 <5 4 J 6

And so on. Here's where things get tricky. My results are text values.
They have commas and less than signs in them, some of the data also
include alphabetical qualifiers (such as "J" above). Is there a way to
compare these text results to the relevant standard and shade them if
they exceed the standard?

I tried conditional formatting. I set the first condition to ignore
anything with a less than sign. Those indicate non-detections. I then
tried something like the following expression for the second condition.

Val([Result 1]) > Val([Standard])

If that condition was met, I wanted to shade the record gray. The
problem is that nothing happened. Nothing gets shaded. I don't think
Access is able to convert these text values to numbers and compare them.

I might be able to go back to my source tables and queries and compare
the numerical values to the standards and put an asterisk or some other
symbol in the result field to denote an exceedance of a standard, but
then I will have an asterisk next to all the exceedances in addition to
shading the value gray.

Does anyone have any ideas? I would be happy to provide more details,
but this post is already rather long. Thank you in advance for any help.

Sincerely,

Tom

 

[Allen Browne]

Tom it would be possible, if messy, to do this. It would involve
concatenating an expression together, wrapping Eval() around it in the
Format event of the report section.

You would need to determine if the result or the standard is null. If not,
trim any leading spaces, parse the operator (=, >=, <=, <>, >, <, etc)
without getting caught on the sign (ascii 43 or 45), add "=" if there is no
operator, and concatenate it together wth the [Standard] into a string, and
wrap Eval() around that so it evaluates to True or False. Then based on the
result of the evaluated expression, set the BackColor of the text box in the
Format event of the (Detail?) section.

A significant problem here is that the data is not normalized:
1. Your fields are not atomic, i.e you have 2 different things in one field
(an operator and a value.) It would simplify things considerably if you used
2 fields for these 2 things: one for the operator (drop-down list of
choices), and the second for the value. The value can now be a true number
field, which also saves lots of problems and helps avoid invalid data.

2. You have repeating fields (Result1, Result2, etc.) Whenever a table has
repeating fields, it always means you need a related table with many records
instead of this table with many fields. (Or perhaps you do have a normalized
table, and have intentionally unnormalized it with a crosstab for your
report.)

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

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

Howdy Folks,

I would like to shade some records in an Access report based on whether
the value for a given record is above a certain level. That's the basic
concern, but let me provide some more details.

My report is a summary of analytical chemistry data for groundwater. I
want to compare the results for various wells against governmental
groundwater standards. The format of the report is shown below:

Chemical Name Standard Result 1 Result 2 Result 3
Benzene 5 1,000 1,500 1,450
Arsenic 3 <5 4 J 6

And so on. Here's where things get tricky. My results are text values.
They have commas and less than signs in them, some of the data also
include alphabetical qualifiers (such as "J" above). Is there a way to
compare these text results to the relevant standard and shade them if they
exceed the standard?

I tried conditional formatting. I set the first condition to ignore
anything with a less than sign. Those indicate non-detections. I then
tried something like the following expression for the second condition.

Val([Result 1]) > Val([Standard])

If that condition was met, I wanted to shade the record gray. The problem
is that nothing happened. Nothing gets shaded. I don't think Access is
able to convert these text values to numbers and compare them.

I might be able to go back to my source tables and queries and compare the
numerical values to the standards and put an asterisk or some other symbol
in the result field to denote an exceedance of a standard, but then I will
have an asterisk next to all the exceedances in addition to shading the
value gray.

Does anyone have any ideas? I would be happy to provide more details, but
this post is already rather long. Thank you in advance for any help.

Sincerely,

Tom

 

[Thomas Campitelli]

Howdy Allen,

Thank you for your response. I had a feeling this would be messy. My
source table is normalized, although the one from which I generate the
report is not. I do a crosstab query on my source table, paste that data
into a new table and then run my report. I have a few requirements for
the final output that makes such a clunky setup necessary. At least, I
think it makes such a setup necessary.

I want to have thousands separators for the numbers, but I don't want to
specify a fixed number of decimal places.

When a chemical is not detected, I want to display the detection limit
preceded by a less than sign. In my source table, the detection limit
exists as a number, not text.

I want to show qualifiers nearby the numbers. I am not that great with
cross tab queries, so I don't think I can crosstab more than one field
in the "data" section. Am I correct in this assertion? If not, I can
make my fields atomic and this problem would become more simple.

Thanks in advance.

Tom

 

[Allen Browne]

Okay that makes good sense of what you described: it is a crosstab result.

I think you will need to write a function that accepts the crosstab value (a
result, including any operator) and the standard, performs the comparsion,
and returns True if the box should be shaded grey. You can then set up the
conditional formatting (CF) to call the function, passing in the relevant
values, and accept the result as an expression. So, the CF condition 1 for
Result1 would look something like this:
Expression ... IsGrey([Result 1], [Standard])

The numbers can be formatted either with the Format() function, or (where
the text box is bound to the number) the Format property of the text box.
Along the lines of:
#,##0

I'm not clear if the crosstab generates pure numbers, and the signs are then
calculated and displayed on the report, or if the crosstab values are text
that includes the sign.

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

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

Howdy Allen,

Thank you for your response. I had a feeling this would be messy. My
source table is normalized, although the one from which I generate the
report is not. I do a crosstab query on my source table, paste that data
into a new table and then run my report. I have a few requirements for the
final output that makes such a clunky setup necessary. At least, I think
it makes such a setup necessary.

I want to have thousands separators for the numbers, but I don't want to
specify a fixed number of decimal places.

When a chemical is not detected, I want to display the detection limit
preceded by a less than sign. In my source table, the detection limit
exists as a number, not text.

I want to show qualifiers nearby the numbers. I am not that great with
cross tab queries, so I don't think I can crosstab more than one field in
the "data" section. Am I correct in this assertion? If not, I can make my
fields atomic and this problem would become more simple.

Thanks in advance.

Tom

Tom it would be possible, if messy, to do this. It would involve
concatenating an expression together, wrapping Eval() around it in the
Format event of the report section.

You would need to determine if the result or the standard is null. If
not, trim any leading spaces, parse the operator (=, >=, <=, <>, >, <,
etc) without getting caught on the sign (ascii 43 or 45), add "=" if
there is no operator, and concatenate it together wth the [Standard] into
a string, and wrap Eval() around that so it evaluates to True or False.
Then based on the result of the evaluated expression, set the BackColor
of the text box in the Format event of the (Detail?) section.

A significant problem here is that the data is not normalized:
1. Your fields are not atomic, i.e you have 2 different things in one
field (an operator and a value.) It would simplify things considerably if
you used 2 fields for these 2 things: one for the operator (drop-down
list of choices), and the second for the value. The value can now be a
true number field, which also saves lots of problems and helps avoid
invalid data.

2. You have repeating fields (Result1, Result2, etc.) Whenever a table
has repeating fields, it always means you need a related table with many
records instead of this table with many fields. (Or perhaps you do have a
normalized table, and have intentionally unnormalized it with a crosstab
for your report.)

 

[Thomas Campitelli]

Howdy Allen,

Thank you again for your reply. With regard to formatting the numbers, I
have some rather specific requirements that the Format command alone
won't meet. I have values that range from 0.05 to 1,000,000. I want to
preserve the decimal places that exist, but I don't want to explicitly
define the number of decimal points. I have this taken care of with some
if statements and Format commands.

The crosstab query is crosstabbing text values, not numbers. I created
another field in my table that contains the formatted number
concatenated with less than signs and qualifiers, if needed.

I took a slightly different approach to this problem. I went back to my
source table and added a new field and ran a few update queries so that
I could compare numbers to numbers.

I now have my concatenated value complete with less than signs, comma
separated number, text qualifier, and a superscript "1" if the value
exceeds the standard.

I wanted to avoid putting another character in the value field, but
given my lack of skills with VBA, I figured I would give in and do this.
Now I can do what I need to do with conditional formatting.

I wouldn't mind trying to accomplish this with VBA, so I may try
figuring that out in the future.

Thank you again for your help, Allen. I wish you an excellent weekend.

Tom