Conditional formatting with like

[guinness]

Hi Guys

What I am trying to do is create conditional formatting to colour fields within a form.

The conditional formatting option built in allows me to colour based on set values i.e. Value=Design turns green. I need to somehow say if Value contains the word Design turn green. This is because in addition to the word Design there will be a variable description. I tried editing the conditional format created by the wizard to Value="*Design*" but that didn't work.

Now I'm trying to write it in VBA code but am still struggling. Here's what I tried but it doesn't like it:

If InStr([Forms]![Query2]![Activity] Like "*Design")>0 Then
[Forms]![Query2]![Activity].BackColor = vbGreen
End If

Any help much appreciated

Guinness

 

[GinaWhipp]

Hmm, need a wee bit more information... Is this a Continuous Form? What version of Access?

 

[vbaInet]

You're not using the Instr() function correctly. LIKE is similar to InStr() and in your case you use one or the other not both.

Expression Is
InStr(1, [Activity], "design") > 0

OR

Expression Is
[Activity] LIKE "*design*"

 

[guinness]

Thanks guys

The form in question is the result a crosstab query with a date field as the column and an activity (i.e Design) as the value.

Based on your feedback I tried the following but still no joy

Private Sub Backcolor()
If ([Forms]![Query2]![Activity] Like "*Design") Then
[Forms]![Query2]![Activity].Backcolor = vbGreen
ElseIf ([Forms]![Query2]![Activity] Like "*Delivery") Then
[Forms]![Query2]![Activity].Backcolor = vbYellow
End If
End Sub

If the activity cell just contained the word Design then I could do it with the conditional format wizard however in this case the value will be something like: Design project title

I feel like I'm close but not quite there.

Thanks guys

 

[vbaInet]

My first post relates to using "Conditional Formatting" menu to do just that. You can't achieve this by writing code in the VBA IDE (or code window).

 

[guinness]

Sorry vbainet but I'm still stuck.

I tried both your suggestions but with no luck. See screenshot attached relating to 01/07/2014

Thanks again for looking

 

[guinness]

Please note I did change the word to Delivery in this instance despite what you see on the screenshot

 

[vbaInet]

If you notice in my post it highlights the word "Expression Is" so when adding a new conditional format instead of using the default "Field Value Is", you change that to "Expression Is".

 

[vbaInet]

And remember that the order of your conditional formats matter as well. So I would advise that you delete all of them, get one working and add the others.

 

[guinness]

Thanks vbaInet

Perhaps it's because the source query was a crosstab but I still can't get this to work. As you suggested I might try and delete all the conditional formatting and start again.

Thanks for your help

 

[vbaInet]

Nothing to do with the source. Just start from scratch and write it properly and it'll work as expected.

 

[guinness]

Haha!

My fault. It is because the query was a crosstab. I'm calling the field Activity as that was where the source information came from. However the Activity is a value in the crosstab field Work_Date. See screen capture:

Now I have a new issue. The form see's each field as an individual date. In the case of the screenshot 02/07/2014. How can I use format painter to copy this to all the other dates. Is there a way to refer to the 'current field' as opposed to a field named 02/07/2014?

:banghead: guinness

 

[vbaInet]

Well this is a different case. The problem here is that the date fields are built on the fly but the textboxes on your form are static. If for example records relating to the date "02/07/2014" gets deleted the textbox that is linked to this date field from the query won't work anymore.

 

[guinness]

The text boxes are created from a table of Working_Days. In order to ensure that each day is displayed whether it contains an entry or not I have created a record encompassing all those dates. What I need to be able to do is copy the formatting from textbox 02/07/2014 to textbox 03/07/2014 if that makes sense. At present my expression includes [02/07/2014] where what I need is a reference to [Current Field]. Is that workable?

 

[vbaInet]

Wouldn't that be a matter of replicating the same code across all the other textboxes?

And going back to the scenario I gave you in my last post, what will happen to your dates next year or even next month?

 

[guinness]

It would involve replicating the same code in every text field but changing the field name. I have dates in my table from now till December 2017. I could use format painter however I would then need to go and change the field name for every single date.

If I could reference the current field name then I could copy across all dates. It's typical Access. There must be a way to do it but it isn't obvious. I want to paint all the formatting from [02/07/2014] to [03/07/2014] but change the expression to read [03/07/2014].

 

[vbaInet]

You're missing the point I'm trying to get across. Can you show me your form in Design View. And also paste the SQL statement of your query.

By the way, Format Painter is independent of Conditional Formatting.

 

[guinness]

Thanks vbaInet

The sql for the query:

TRANSFORM First(Resourcing.Activity) AS FirstOfActivity
SELECT Resourcing.Trainer_Name, Resourcing.Team, Resourcing.Duration
FROM Activities, Resourcing INNER JOIN Dates ON Resourcing.Start_Date = Dates.[Work_Dates]
WHERE (((Dates.Work_Dates)>#1/1/2014# And (Dates.Work_Dates)<#1/1/2014#+352))
GROUP BY Resourcing.Trainer_Name, Resourcing.Team, Resourcing.Duration
PIVOT Dates.Work_Dates;

I'll create a new form and send a screenshot

 

[vbaInet]

Make a copy of your form (i.e. copy and paste) and remove the bits you don't want us to see.

 

[guinness]

See picture attached.

Using format painter I can copy the formatting from one field to the other however the expression referring to the Date doesn't change.

What I am looking for is some way to say [Current Field] Like "*Delivery*" rather than [10/01/2014] Like "*Delivery*".

That's why I originally thought that there might be some VBA code that could apply to the whole form.

Any help appreciated

Guinness