Erratic conditional formating.

[LOUISBUHAGIAR54]

I have a report in Access 2007. This has fields with names of patients, and clinical details. It has three subreports. The first has laboratory results of blood tests. On the report there are reference values with lower and upper limits of normal laboratory values. The blood results occupy another field. I have added conditional formatting so that when the lab result is outside the normal range the value appears underlined with a shaded background. The problem is that the conditional formatting works well most of the time.... but not always. By that I mean it works well with some results but not with other entries in the SAME report !

What could the problem be?


Many thanks.


Louis.

 

[pbaldy]

Hard to say without seeing an example. My first guess is a data related issue. Is there a pattern to the ones that fail?

 

[LOUISBUHAGIAR54]

Hi Paul,

Thanks for replying and for thinking about my problem.

No I cannot see a pattern. All I can notice is that some results are rightly formatted as expected, while others are wrongly formatted. Generally they are wrongly formatted when they are within the normal range.

I am sure that you appreciate that the conditional formatting in the design view of the subreport is only made in a single field. So I cannot see why it should work correctly on one result and not in another.

My database is pretty crowded with forms, reports, tables, class modules etc. So I cannot see how I can provide an example. I know you are an active expert on this forum so maybe you can guide me on how I can furnish an example.

The field containing the result is formatted as a General number.

Many thanks again.

Louis

 

[pbaldy]

The data type of the field is more relevant than the format. You could get odd comparisons with a text data type. Is it possible to export the report and the query/table it's based on out of the main db to provide a sample?

 

[LOUISBUHAGIAR54]

Hi Paul,


Many thanks for replying. I have been trying to solve the problem since. I will try to export the main report, sub-report and the query on which it is based.

Sorry to sound so naïve. Can you direct me on how to export these items separately from the database ?


Many thanks.


Louis

 

[pbaldy]

In 2007 it would be on the External Data tab. You can export from the actual db or import from a new db.

 

[LOUISBUHAGIAR54]

Ok. So I removed all objects from the database except those that concern us. By that I mean the subreport with the source query and tables. On opening the sub-report in report view on can see that the conditional formatting is erratic.

I hope that I have written up the conditional formatting correctly.

I hope this export goes well cause I have only done it once before. Here goes. Kindly press the SHIFT key as you open. I have moved the relevant tables from the BEnd and linked them again. I hope you get a good specimen.

Many thanks for your patience.

Louis

 

[Dick7Access]

My database is pretty crowded with forms, reports, tables, class modules etc. So I cannot see how I can provide an example. I know you are an active expert on this forum so maybe you can guide me on how I can furnish an example.
Louis

Hi Louis,
Last time I had a problem that needed someone to look at the whole db I took out the real names and enter a few fake name, and duplicated the problem. I then uploaded it toMediaFire, which is free, www.mediafire.com/ and they were then able to look over all the code.
Dick S.

 

[pbaldy]

As I mentioned, you'll get odd results if your fields are text, which yours are (the upper and lower limit fields anyway). Given that you want to do numeric comparisons, first option would be to use a numeric data type. Failing that, you'll need to convert the value for the report, using CDbl() or the appropriate data type.

 

[Dick7Access]

Louis

BTW Louis, very easy quick way to take all real data out. Post if you don't know how.

 

[gemma-the-husky]

ignore all this please

 

[Dick7Access]

ignore all this please

Dave,
What are we ignoring?

 

[pbaldy]

what may be happening is that your code is setting a format, and then not testing/resetting further rows. the format is not a one-time thing - it affects all rows until you change it to something else.

Conditional formatting is available on reports, and was used here. Why speculate on a code-based solution that wasn't even used?

 

[LOUISBUHAGIAR54]

Hi,

Many thanks for all your comments. I changed the format of the upper and Lower limit fields to numeric etc. And now the conditional formatting is working according to plan. I suppose the software was comparing numbers with texts and was becoming erratic.

Thanks Paul.

Louis

 

[pbaldy]

Happy to help! It's not erratic, it's alphabetic sorting instead of numeric. Alphabetically, 2 is greater than 1 but it's also greater than 10, 100, etc. With a text field you will get alphabetic sorting.