Highlighting values in a table above a given threshold

[IANGRAND]

I have an ongoing project where i am receiving weekly borehole data (30 in total) for a landfill site. The data is stored in an access database, where graphs automatically update on a weekly basis. However, within the tables i need to highlight values that are above a certain threhold ie methane above 5 percent, this value needs to be highlighted in red.

Is there a way using an if stement that i can search through a series of tables and highlight a value above this threshold?

Cheers

Ian

 

[FancyPrairie]

I don't believe you can do what you want via a table. You're going to have to create a form whose recordsource is based on your table. Then, via the OnCurrent event of the form, set your colors based on your conditions. For example, if your field name is Text1, then it would look like this:

If (Text1 > whatever) then
Text1.ForeColor = 255 'Red
Else
Text1.ForeColor = 0 'Black
End If

 

[uscitizen]

Further to FancyPrarie's, I think you can also use 'Conditional Formating' settings which are probably easier done. With your cursor sitting in a control (whether you're in 'Design' view or 'Open' view doesn't seem to matter), click on 'Format' from the Menu Bar. The rest is sort of self-explanatory. G'dluck.

 

[IANGRAND]

FancyPrarie

I understand and like the idea of using vb but Im somewhat confused by the solution. The field i am checking is called "CH4 (%)", which i assume i have to set as a variable (in your instance Text1)

Also do i need to set a loop so that all values in the field are checked or is this not necessary in access?

Cheers

Ian

 

[FancyPrairie]

First, my method will not work if you are displaying your form in datasheet view. The only way you can change the forecolor in datasheet view is as USCitizen suggested (Conditional Formatting (which you can also do programmatically (sort of))). If you are only going to display the form in Form View, then my method will work.

Second, I would not use the % symbol as part of a field name. Access uses it as one of its wildcards. What you're doing may not pose a problem, but I would avoid it.

Third, I'm a little confused as to what you're doing. If I understand you correctly, you have 30 fields (one of which is name "CH4 (%)&quot. And each field needs to be checked to determine if it is out side the valid range. And, if so, then set it to red, else some other color. If my understanding is correct, then you will have to enter 30 If-Then-Else statements to check all of the fields. And, if this is true, I would consider renaming my fields so that I could do it in a loop or use Conditional Formatting.

If you choose to rename, you don't have to rename the fields within the table, just their control names on the form. For example, if you include table field CH4 (%) on your form, then name it something like txtField1 (note that the control source of txtField1 would be CH4 (%)). Then the next control would be txtField2, and so on. Your loop would look something like this:

For i = 1 to 30
If (Me("txtField" & i) > whatever) Then
Me("txtField" & i).ForeColor = 255 'Red
Else
Me("txtField" & i).ForeColor = 0 'Black
End If
Next i

 

[IANGRAND]

There are 6 fields, these are Relative Pressure, CH4, CO2, O2, Absolute Pressure and date. This information is recorded on a weekly basis for 30 boreholes. Each week the new results are added to the database, as a new date is added graphs are automatically updated for each of the boreholes.

With the previous method I dont think it will work. In the form view i am unable to rename each field as on a weekly basis this would require some 180 fields to be renamed. Also if i name the field as txtfield it appears that all fields are named as this. Since when i apply the code all values are coloured red as the first value in the first text box is above the threshold. Is there anyway that i can add a script to the form so that as the information is loaded into the form the value in the new txtfield is the one that is read?

ps: is it possible that i could forward a copy of the databse as it is difficult to explain.

Cheers

Ian Grand

 

[FancyPrairie]

Have you tried using Conditional Formatting?