Change format of text box depending on value

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

I have a report with c. 100 records and a number of fields
displayed in text boxes.

I want the background colour of one of the text boxes to
be red if the text for that record includes the
word "Not". Otherwise, I want it to be as normal,
unformatted.

The condition must be checked for with each record as the
records are not sorted in any fashion.

Can anybody help?
 
Andrew said:
I have a report with c. 100 records and a number of fields
displayed in text boxes.

I want the background colour of one of the text boxes to
be red if the text for that record includes the
word "Not". Otherwise, I want it to be as normal,
unformatted.

The condition must be checked for with each record as the
records are not sorted in any fashion.
Click to expand...

In A2K or later you could probably use Conditional
Formatting to do this. In all versions you can use code
somthing like:

If Me.thetextbox Like "*not*" Then
Me.thetextbox.BackColor = vbRed
Else
Me.thetextbox.BackColor = vbWhite
End If
 
Andrew,
Which Access Version?
Access 2000 or later, set the Conditional formatting for that control.
Set the Condition 1 Dropdown to
Expression Is
Set the text box along side the drop-down to:
InStr([ControlName]," Not ") > 0
Set the Control color for both the Normal and the Conditional format.

If you are using Access 97 or older:
Set the Detail Format event (if the control is in the Detail section) to:
If InStr([ControlName], " Not ") > 0 Then
[ControlName].BackColor = vbRed
Else
[ControlName].BackColor = vbWhite
End If

Add a space around the word "Not" so that it will not
format the control if the word 'Nothing' or 'What-not, etc.,
was found.
 
Andrew,

Just to add to Fred's suggestion, if you want to include instances
where Not is either the first or last word in the field, the condition
should be set to...
[FieldName] Like "*not*"

However, I interpreted your original post to mean if the word Not
appears in any of the fields. If so, use a condition like...
[Field1] & "/" & [Field2] & "/" & ... & [FieldN] Like "*not*"

- Steve Schapel, Microsoft Access MVP
 
Back
Top