Change format of text box depending on value

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?
 
M

Marshall Barton

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.

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
 
F

Fredg

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.
 
S

Steve Schapel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top