Changing color in a report

[mmap]

Any way we can change the color for a field in a report from the vba code.
For example if the field contains an 'a' then have the color blue but if the
field contains a w then use red. TIA

 

[Wayne Morgan]

Yes, but if you have Access 2000 or newer, it would be easier to use the
built-in Conditional Formatting. Open the report in design view, right click
the control, and choose Conditional Formatting. Make the desired settings.

In code, use the Format event for the report section the control is in. You
would then use code similar to:

Select Case Me.txtMyTextbox
Case "a"
Me.txtMyTextbox.ForeColor = vbBlue
Case "w"
Me.txtMyTextbox.ForeColor = vbRed
Case Else
Me.txtMyTextbox.ForeColor = vbBlack
End Select

Wayne Morgan
MS Access MVP

 

[mmap]

Ok the conditional formatting is what we want. However, we set the field to
'is equal to' & the character to an 'a' (lower case) BUT it converts upper
& lower case a's. What are we missing? TIA

Yes, but if you have Access 2000 or newer, it would be easier to use the
built-in Conditional Formatting. Open the report in design view, right click
the control, and choose Conditional Formatting. Make the desired settings.

In code, use the Format event for the report section the control is in. You
would then use code similar to:

Select Case Me.txtMyTextbox
Case "a"
Me.txtMyTextbox.ForeColor = vbBlue
Case "w"
Me.txtMyTextbox.ForeColor = vbRed
Case Else
Me.txtMyTextbox.ForeColor = vbBlack
End Select

Wayne Morgan
MS Access MVP

 

[Wayne Morgan]

Access is not case sensitive in most cases, so if you check for "a", "A"
will also satisfy the condition. To work around this, try using the Asc()
and Chr() functions. The ASCII for "A" is 65 and for "a" it is 97. You will
probably need to set the drop down to Expression Is and then use the
expression:

Asc([txtMyTextbox]) = Asc("a")

 

[John Spencer]

The fact that Access is not case sensitive. You will have to use an
expression instead of field is equal to. Something like

Asc(SomeValue) = Asc("a')
or
Instr(1,SomeValue,"a",0) = 1

 

[mmap]

Bingo thanks.

Access is not case sensitive in most cases, so if you check for "a", "A"
will also satisfy the condition. To work around this, try using the Asc()
and Chr() functions. The ASCII for "A" is 65 and for "a" it is 97. You will
probably need to set the drop down to Expression Is and then use the
expression:

Asc([txtMyTextbox]) = Asc("a")