Colour coding - Access 2000 1

[Lynne41]

Hi there

Can anyone help me with Conditional formatting based on a criteria?

I have set up a conditional format for a report.The difference between two number fields returns a number depending on what the answer is.

I have 4 conditional formats to set but MS Access 2000 only lets me set 3!

I have tried doing the four nested IIF's but the cond. format. doesn't recognise RGB or Vb[colour].

I have tried doing it in VBA but cannot get that to work either - I only know very basic VBA.

Can anyone help?

Thanks
Lynne

 

[Lynne41]

Sorry - the second paragraph should read:

I have set up a conditional format for a report. The difference between two number fields returns a colour in one of the text fields depending on what the answer is.

Apologies for confusion!

Lynne

 

[randysmid]

Lynne,
I wrote an FAQ on how to use color in forms and reports. You can find it at: faq702-2711

There is one example where you can use the If statement to change a color depending on a condition. I am not sure why you need to use IIF's for this.

HTH,
Randy Smith, MCP
California Teachers Association

 

[Lynne41]

Hi Randy

Thanks for coming back to me. I have looked at your FAQ but I don't know how to write in VBA.

Basically what I'm trying to do is this: txt2003All = name of field.

If difference between txt2003All and (other txtbox) between 0.3 and 0.4 then
txt2003All = RBG(255,0,0)
else if
difference between txt2003All and (other txtbox) >0.5 then
txt2003All = RBG(whatever)
else if
difference between txt2003All and (other txtbox) between -0.3 and -0.4 then
txt2003All = RBG (whatever)
else if
difference between txt2003All and (other txtbox) >-0.4 then
txt2003All = RBG (whatever)
else
txt2003All = RGB (whatever)

I have no idea how to write this in VBA - including declarations etc., as I keep getting "unknown identifiers" etc.

Any help would be much appreciated.

Lynne

 

[Norris68]

Your VBA code will want to go into the Format event e.g. Detail_Format. The code will be pretty much as you have written it.

Dim MyDiff As Double
MyDiff = txt2003All - (other textbox)
If MyDiff > 0.5 then
txt2003All.ForeColor = RGB(whatever)
ElseIf MyDiff >= 0.3 and MyDiff <= 0.4 then
txt2003All.ForeColor = RGB(255,0,0)
ElseIf MyDiff <= -0.3 and MyDiff >= -0.4 then
txt2003All.ForeColor = RGB (whatever)
ElseIf MyDiff < -0.4 then
txt2003All.ForeColor = RGB(whatever)
Else
txt2003All.ForeColor = RGB(whatever)
End If

 

[Lynne41]

Thanks Norris for your help - works a treat!