conditional formatting based upon "limits" in a table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to use conditional formatting based upon limits that come from
a table? I am asking because I do not want to maintain these limits in 2
separate places, on the report condtional formatting for that cell and in the
table. I would like to write some code and get these limits directly from a
table. Any suggestions with examples of code would be much appreciated!

Thank you!
Chris
 
You don't really give us enough detail to answer your question.

Lets say you have a field (temperature) and you want it to be red if over
100 and blue if under 50 and black for any others. How do you plan to put
this in a table?

Give us an example of your actual data and the "limits" and how you plan to
put these limits in a table. Also, if you put them in a table, how will
Access know what color to make the data?

I doubt this can be done very easily, but give us some details.

Rick B
 
Sorry,

I have a table called LimitTable. There is TestNum as primary key,
MinLimit, MaxLimit, and test description fields in this table. I use the
MinLimit and MaxLimit value in other reports for stat calculations. For
those stat calculations, I use the Dlookup function:
=DLookUp("[MaxLimit]","LimitTable","[TestNum]=26"), where TestNum = 26, or
row 26 in the limit table.

I was thinking I could use something like this:

If Me![OATSensor] between (DLookup("[MaxLimit]", "LimitTablee",
"[TestNum]=26")) and (DLookUp("[MinLimit]","LimitTable","[TestNum]=26")) Then
Me![OATSensor].ForeColor = vbRed
Else
Me![OATSensor].ForeColor = vbBlack
End If

I get an error with the "between" statement in VB

Sorry for the confustion. Chris
 
I figured it out. Since I was already pulling the upper and lower limts in
from a table already, with the method as stated below, I just used the field
name from that limit, and inderted the field name into the conditional
formatting section, and used "is not between". It works perfectly.

Chris

Dirty70Bird said:
Sorry,

I have a table called LimitTable. There is TestNum as primary key,
MinLimit, MaxLimit, and test description fields in this table. I use the
MinLimit and MaxLimit value in other reports for stat calculations. For
those stat calculations, I use the Dlookup function:
=DLookUp("[MaxLimit]","LimitTable","[TestNum]=26"), where TestNum = 26, or
row 26 in the limit table.

I was thinking I could use something like this:

If Me![OATSensor] between (DLookup("[MaxLimit]", "LimitTablee",
"[TestNum]=26")) and (DLookUp("[MinLimit]","LimitTable","[TestNum]=26")) Then
Me![OATSensor].ForeColor = vbRed
Else
Me![OATSensor].ForeColor = vbBlack
End If

I get an error with the "between" statement in VB

Sorry for the confustion. Chris

Rick B said:
You don't really give us enough detail to answer your question.

Lets say you have a field (temperature) and you want it to be red if over
100 and blue if under 50 and black for any others. How do you plan to put
this in a table?

Give us an example of your actual data and the "limits" and how you plan to
put these limits in a table. Also, if you put them in a table, how will
Access know what color to make the data?

I doubt this can be done very easily, but give us some details.

Rick B
Click to expand...
Click to expand...
 
Back
Top