CONDITIONAL FORMATING

[Guest]

I have a form that pulls GRADE and AvgOfASH VALUE from a querry. The GRADE
is shown in the reportheader and the AbgOfASH VALUE is shown in the details
section. I want the AvgOfASH VALUE to show up in red if it is out of spec
and green if it is in spec. The high and low limits are stored in a table
called COMPUND GRADES in the HIGH LIMIT AND LOW LIMIT fields. I thought that
following code using a DlookUp funciton to pull the limits would work and
placed it in the Onformat event of the Details section. It does not get past
the first line of the if staement. Any ideas?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [AvgOfASH VALUE] < DLookup("[LOW LIMIT]", "[COMPOUND GRADES]", "[GRADE] =
" & [GRADE]) Then
With Me![AvgOfASH VALUE]
.ForeColor = vbRed
End With
ElseIf [AvgOfASH VALUE] > DLookup("[HIGH LIMIT]", "COMPOUND GRADES",
"[GRADE] = " & [GRADE]) Then
With Me![AvgOfASH VALUE]
.ForeColor = vbRed
End With
Else
With Me![AvgOfASH VALUE]
.ForeColor = vbGreen
End With
End If
End Sub

 

[Guest]

I solved the issue by including the low and high limits as part of the query,
the should really be on the COA in any case. I do wish I could figure out
shy the way I was trying to do it did not work.

Ed

 

[Marshall Barton]

You found the preferred way to make those values available
to the report.

As to why the DLookup didn't work, the only thing I can
thing of is that the Grade field is a Text field. It
probably would have helped if you had explained what error
you got, instead of just "does not get past the first line".
Presumably the message was somthing about a type mismatch
error??

Anyway, if Grades really is a Text field, then the DLookup
should be:

DLookup("[LOW LIMIT]", "[COMPOUND GRADES]", "[GRADE] = '" &
[GRADE] & "'")
--
Marsh
MVP [MS Access]

I solved the issue by including the low and high limits as part of the query,
the should really be on the COA in any case. I do wish I could figure out
shy the way I was trying to do it did not work.

I have a form that pulls GRADE and AvgOfASH VALUE from a querry. The GRADE
is shown in the reportheader and the AbgOfASH VALUE is shown in the details
section. I want the AvgOfASH VALUE to show up in red if it is out of spec
and green if it is in spec. The high and low limits are stored in a table
called COMPUND GRADES in the HIGH LIMIT AND LOW LIMIT fields. I thought that
following code using a DlookUp funciton to pull the limits would work and
placed it in the Onformat event of the Details section. It does not get past
the first line of the if staement. Any ideas?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [AvgOfASH VALUE] < DLookup("[LOW LIMIT]", "[COMPOUND GRADES]", "[GRADE] =
" & [GRADE]) Then
With Me![AvgOfASH VALUE]
.ForeColor = vbRed
End With
ElseIf [AvgOfASH VALUE] > DLookup("[HIGH LIMIT]", "COMPOUND GRADES",
"[GRADE] = " & [GRADE]) Then
With Me![AvgOfASH VALUE]
.ForeColor = vbRed
End With
Else
With Me![AvgOfASH VALUE]
.ForeColor = vbGreen
End With
End If
End Sub