Like statment with Conditional Formatting

[TallMan]

Good Morning All,

I have a subform that is linked to a "notes" table. What I need to happen is if one of the notes is like "*Approved*" then the notes_field on the subform turns green.

I have tried the conditional formatting setting that Access 2007 provides but I am thinking I may need to go VBA on this one.

I have tried a variety of code but can you tell me if I am on the tight path?

If instr([Forms]![TblNotes Subform]![Notes_Field],"*Approved*") > 0 then
[Forms]![TblNotes Subform]![Notes_Field].backcolor = vbgreen
end if

It would be AWESOME if I could get just the word to highlight in a color as opposed to the whole string but I am not sure if that is possible.

Thank you all!

Tallman

 

[boblarson]

1. You can't get the single word to highlight.

2. Is this a memo field? I'm not sure CF will work on a memo but if it can, it should be:

Expression Is

and the value of the expression:


[Notes_Field] Like "*Approved*"

 

[TallMan]

Bob,

Worked like a charm! It is infact a text field as this typically not a "wordy" field.

Thank you for your help!

 

[missinglinq]

Interesting! Conditional Formatting can be used with Memo Fields, but I couldn't get Like to work with it, in 2003. But

Expression Is

InStr([MemoFieldName],"Approved")>0

did work.

Linq ;0)>

 

[TallMan]

Great! Thank you both for your time and help!

 

[DCrake]

If using A2007 you could change the memo field to Rich text type and then you could highlight the word approved in green.

 

[TallMan]

David,

I changed the notes field to a memo field with rich text and the whole field is still highlighting in green. Is there something else I need to change to have just the word highlight? I am using the statement below and the

Expression is [Notes] Like "*Approved*"


Currently is set to have the background turn green. Is it possible to have the background of just the word "Approved" turn green?

Thank you

 

[DCrake]

Whilst the text box is on the form in form view highlight the word approved which should bring up the format painter box and color acordingly.

 

[DCrake]

See image

 

[TallMan]

Hopefully one last question. Is this something that you have to manually color everytime or does your system recognize that word and code it green? I cannot get it to recognize the word for future notes. Looks like the associate would have to manually update that.

Thank you.

 

[DCrake]

That's one question I can't answer, never user RTB before, however I suppose there is a solution out there.