conditional formatting on text string

P

paulbowles

Hi,

I have an Access 2003 db that has a report in it. I am trying to do a
conditional format on one of the fields so that if the text in the
text box has the string "GCA" (w/out quotes) in it, that it will fill
back color with blue, and the fore color (or the text) white. The
field is named "Club Position"

Looks like it can't be done using the "Field Value Is" condition, but
perhaps there is an "expression Is" that I can use.

Any help is much appreciated.

Thanks.
 
R

Rob Parker

Conditional formatting using "Field Value Is" should work perfectly. Select
"equal to" in the next drop-down, and enter "GCA" - with the quotes, since
it's a text string - in the last box. Then set the back colour and fore
colour to what you want.

HTH,

Rob
 
F

fredg

Hi,

I have an Access 2003 db that has a report in it. I am trying to do a
conditional format on one of the fields so that if the text in the
text box has the string "GCA" (w/out quotes) in it, that it will fill
back color with blue, and the fore color (or the text) white. The
field is named "Club Position"

Looks like it can't be done using the "Field Value Is" condition, but
perhaps there is an "expression Is" that I can use.

Any help is much appreciated.

Thanks.
Click to expand...

Do you mean the field can contain text such as "NMSGCAUIE" and you
wish to color the control?

Condition1
Expression Is
InStr([ThisControlName],"GCA")>0

If you mean the field should contain ONLY "GCA" then Rob's answer is
what you should use.
 
S

slowly learning

fredg said:
Hi,

I have an Access 2003 db that has a report in it. I am trying to do a
conditional format on one of the fields so that if the text in the
text box has the string "GCA" (w/out quotes) in it, that it will fill
back color with blue, and the fore color (or the text) white. The
field is named "Club Position"

Looks like it can't be done using the "Field Value Is" condition, but
perhaps there is an "expression Is" that I can use.

Any help is much appreciated.

Thanks.
Click to expand...

Do you mean the field can contain text such as "NMSGCAUIE" and you
wish to color the control?

Condition1
Expression Is
InStr([ThisControlName],"GCA")>0

If you mean the field should contain ONLY "GCA" then Rob's answer is
what you should use.
Click to expand...
Fred,

How would one accomplish this with more than 4 or more conditions? Multiple
colors?
I've tried using code in other examples where IIf ... else IIf ... is used
but cannot seem to get them working
 
F

fredg

fredg said:
Hi,

I have an Access 2003 db that has a report in it. I am trying to do a
conditional format on one of the fields so that if the text in the
text box has the string "GCA" (w/out quotes) in it, that it will fill
back color with blue, and the fore color (or the text) white. The
field is named "Club Position"

Looks like it can't be done using the "Field Value Is" condition, but
perhaps there is an "expression Is" that I can use.

Any help is much appreciated.

Thanks.
Click to expand...

Do you mean the field can contain text such as "NMSGCAUIE" and you
wish to color the control?

Condition1
Expression Is
InStr([ThisControlName],"GCA")>0

If you mean the field should contain ONLY "GCA" then Rob's answer is
what you should use.
Click to expand...
Fred,

How would one accomplish this with more than 4 or more conditions? Multiple
colors?
I've tried using code in other examples where IIf ... else IIf ... is used
but cannot seem to get them working
Click to expand...

Access offers 3 Conditions.
You might want to look through Stephen Lebans' site to see if he has a
sample Db to extend the conditions.
http://www.lebans.com
 
S

slowly learning

fredg said:
fredg said:
On 16 Mar 2007 22:02:20 -0700, (e-mail address removed) wrote:

Hi,

I have an Access 2003 db that has a report in it. I am trying to do a
conditional format on one of the fields so that if the text in the
text box has the string "GCA" (w/out quotes) in it, that it will fill
back color with blue, and the fore color (or the text) white. The
field is named "Club Position"

Looks like it can't be done using the "Field Value Is" condition, but
perhaps there is an "expression Is" that I can use.

Any help is much appreciated.

Thanks.

Do you mean the field can contain text such as "NMSGCAUIE" and you
wish to color the control?

Condition1
Expression Is
InStr([ThisControlName],"GCA")>0

If you mean the field should contain ONLY "GCA" then Rob's answer is
what you should use.
Click to expand...
Fred,

How would one accomplish this with more than 4 or more conditions? Multiple
colors?
I've tried using code in other examples where IIf ... else IIf ... is used
but cannot seem to get them working
Click to expand...

Access offers 3 Conditions.
You might want to look through Stephen Lebans' site to see if he has a
sample Db to extend the conditions.
http://www.lebans.com
Click to expand...

Thanks for all of the input, after searching the internet I finally found
the answers I was looking for. They were in this newsgroup all along,
spelling can make a world of difference when typing/modifying code.
If anyone else is having trouble with conditional formatting here is an
example of what I ended up with, not sure if it's the best approach but it is
working

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If InStr([Description], "Material") > 0 Or InStr([Description],
"Supplies") > 0 Then
[PO Number].BackColor = vbRed
[PO Number].ForeColor = 16777215
Description.BackColor = vbRed
Description.ForeColor = 16777215
Notes.BackColor = vbRed
Notes.ForeColor = 16777215
Status.BackColor = vbRed
Status.ForeColor = 16777215
[Date Entered].BackColor = vbRed
[Date Entered].ForeColor = 16777215
[PO Created].BackColor = vbRed
[PO Created].ForeColor = 16777215
Company.BackColor = vbRed
Company.ForeColor = 16777215

ElseIf Instr([PO Number], "1500") > 0 or Instr([PO NUmber], "1501") > 0
Then
[PO Number].BackColor = 16777215
[PO Number].ForeColor = vbRed
Description.BackColor = 16777215
Description.ForeColor = vbRed
Notes.BackColor = 16777215
Notes.ForeColor = vbRed
Status.BackColor = 16777215
Status.ForeColor = vbRed
[Date Entered].BackColor = 16777215
[Date Entered].ForeColor = vbRed
[PO Created].BackColor = 16777215
[PO Created].ForeColor = vbRed
Company.BackColor = 16777215
Company.ForeColor = vbRed

Else
[PO Number].BackColor = 16777215
[PO Number].ForeColor = vbBlack
Description.BackColor = 16777215
Description.ForeColor = vbBlack
Notes.BackColor = 16777215
Notes.ForeColor = vbBlack
Status.BackColor = 16777215
Status.ForeColor = vbBlack
[Date Entered].BackColor = 16777215
[Date Entered].ForeColor = vbBlack
[PO Created].BackColor = 16777215
[PO Created].ForeColor = vbBlack
Company.BackColor = 16777215
Company.ForeColor = vbBlack
End If

End Sub

Once again thanks for the help
 

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

Similar Threads

Need help modifying code 0
Change text box background color depending on value in Label Reports 0
Creating Labels Over 3-Up Image 2
Hide a text box when condition is met 1
Conditional Formatting with a Date Function 1
Make a Control Dissapear with Conditional Formatting? 3
Problem with MS Word paragraph and character styles: format getting messed up 2
Conditional Formatting with VBA 4

Top