Help with conditional formatting

Last week an expert helped me with conditional formatting of a text box.  His solution was

https://www.experts-exchange.com/questions/29080620/Make-field-background-yellow-if-the-field-contains.html?anchor=a42448200¬ificationFollowed=203165381#a42448199

But now I need a solution for when there is either [], or [ and any text in beween ].
SteveL13Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
OK. Then Ryan's (reduced) method should work:

InStr([Description],"[")>0 And InStr([Description],"]")>InStr([Description],"[")

Open in new window

0
 
bfuchsCommented:
try instr(YourFieldName,"[")>0 and instr(YourFieldName,"]")>0
0
 
Ryan ChongCommented:
you can also enhance it in existing Expression Conditional Formatting to:

InStr([FirstName],"[")>0 And InStr([FirstName],"]")>0 And InStr([FirstName],"[")<InStr([FirstName],"]")

Open in new window


in case you wish to have a pattern of "[" follow by "]" . in this case "something ] ?? [ whatever " will not be highlighted.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Gustav BrockCIOCommented:
Adjust it to for Expression Is  to read:

Left([FirstName],1)="[" And Right([FirstName],1)="]"

Open in new window

0
 
SteveL13Author Commented:
bfuchs:  Did not work.
Ryan:  Did not work.  In fact, it locked up the form I'm trying to use it with and the form wouldn't even open,

Gustav:  It worked except where the was [None.] in the field.  ????
0
 
Gustav BrockCIOCommented:
Strange. Is "None." a field name?
0
 
SteveL13Author Commented:
No. "Description" is the field name.
0
 
SteveL13Author Commented:
[None.] was a string of characters in the middle of the text in the field.
0
 
Gustav BrockCIOCommented:
So "None." is the content?
I have no explanation.
0
 
Gustav BrockCIOCommented:
Try with these variations:

Left(([Description]),1)="[" And Right(([Description]),1)="]"

Left(Nz([Description]),1)="[" And Right(Nz([Description]),1)="]"

Open in new window

0
 
SteveL13Author Commented:
Neither worked.  So I took a look at an example Excel file I have.  The conditional formatting that works is:

Cell value contains '[*]'

It looks like the asterisk is a wildcard.  ????
0
 
Gustav BrockCIOCommented:
That seems better.
I tried with Like "[*]" but that didn't work.
0
 
SteveL13Author Commented:
So maybe it can't be done in Access?
0
 
Gustav BrockCIOCommented:
A final attempt:

Hex(Asc([Description])) & Hex(Asc(Right([Description],1)))="5B5D"

Open in new window

0
 
SteveL13Author Commented:
Sorry.  Not quite.  It worked with this text in the entire field:

[Provide 2" thick rigid insulation 2' wide at perimeter.] [None.]

But not with:

Saw-cut [Troweled] [Pre-formed] joints at 25' [20'] centers, 600 SF max area. Depth of joints to be 1/4 of slab thickness.
0
 
Gustav BrockCIOCommented:
But that is correct as to your definition:

when there is either [], or [ and any text in beween ].

In your last example, text follows the trailing "]"
0
 
SteveL13Author Commented:
I'm sorry.  My definition was incorrect.  It has to format anytime there is a [ and a ] anywhere in the text field.  Sometimes they'd be together.  Sometimes separated by text.  Sometimes multiple "sets" in the field.  Confusing.
0
 
SteveL13Author Commented:
Ryan's did not work as presented.  Your suggestion does work.
0
 
Ryan ChongCommented:
Ryan's did not work as presented.  Your suggestion does work.
it actually works well for me.



same condition:

InStr([FirstName],"[")>0 And InStr([FirstName],"]")>0 And InStr([FirstName],"[")<InStr([FirstName],"]")

Open in new window


or I missing something here?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.