comparing values in a report

[martinmike2]

I know that I am going to feel dumb when I get an answer to this
question, but I just can't come up woth the answer.

I am trying to compare a textbox 1's value to the value of two other
textbox's. If textbox 1's value is equal to either textbox 2 or 3,
then do nothing, but if textbox 1 is not equal to either textbox 2 or
3, then shade it red. So far I can get it to shade red is textbox 1
is not equal to textbox 2, but when I try to add in textbox3, i get
every textbox1 turning red.

Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngRed As Long
lngRed = RGB(255, 0, 0)
If Not (Me.DNEC1 = Me.r_pnec) Or (Me.DNEC1 = Me.r_snec) Then
Me!DNEC1.BackColor = lngRed
End If
End Sub

 

[John Spencer]

You need to check and set the backcolor back to the correct color (white?) if
the condition is not met.

With your existing code, once the condition is met for a record, the color of
the control will be red for the current record and all subsequent records.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngRed As Long

lngRed = RGB(255, 0, 0)

If Not (Me.DNEC1 = Me.r_pnec) Or (Me.DNEC1 = Me.r_snec) Then
Me!DNEC1.BackColor = lngRed
else
Me!DNEC1.BackColor = vbWhite
End If
End Sub

You could use conditional formatting on the control DNEC1 if you are using
Access 2000 or later. That way you don't need any code at all.

Select the DNEC1 control
Select Format: Conditional Formatting from the menu
Expression is: Not ([DNEC1] = [r_pnec] Or [DNEC1] = [r_snec])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[martinmike2]

John,

I tried the conditional formatting. It seems to work except for one
thing. where DNEC1 = 0000, there is no conditioning. Those rows just
stay with the default setting. Is this because 0000 could be seen by
access as a custom format?

 

[martinmike2]

This also occurs where DNEC1 is null

 

[John Spencer]

If DNEC1 is null then the comparison will fail. So you will need to expand
your criteria.

Expression is: Not ([DNEC1] = [r_pnec] Or [DNEC1] = [r_snec]) Or DNEC1 Is Null

Can r_Pnec and r_Snec be Null? IF so you may need to use something along the
lines of

Expression is: Not (NZ([DNEC1]) = Nz([r_pnec]) Or Nz([DNEC1]) = Nz([r_snec]))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[martinmike2]

John,

You are a godsend, that worked perfectly, thanks.