Using wild cards with Conditional Formatting

[jereece]

Can you use a wild card (e.g. *) in conditional formatting. I have a report that has prirority codes. I want to highlight any that are *1a with yellow and any *1b green. So for example, any items that have a priority code I1a, O1a, P1a, Z1a, etc would be highlithted yellow and any I1b, O1b, P1b and Z1b would be highlighted green. I can not seem to get * to work as a wild card in conditional formatting. I have tried "*1a" and *"1a" but neither seems to work. Any suggestions?

Thanks,
Jim

 

[pbaldy]

Sure; using Expression Is:

[FieldName] Like "*1a"

 

[jereece]

That does not seem to work either. See attached.

 

[pbaldy]

How does it not work? See attached for working example.

 

[jereece]

Not sure. Your example is in a form and mine is in a report, but that should not make a difference. See attached image. Am I missing somethign?

I really appreciate the help.

 

[pbaldy]

It should work the same for a form or report. What you have looks correct. Can you post a sample db?

 

[jereece]

The report I have pulls data from an SQL database, not Access. Let me put together a sample using local data and see if that works. It will be tomorrow before I can do that. Do you want me to post it here or email to you?

Thanks for your effort.

 

[pbaldy]

You can post it here. That way if I'm not around somebody else might get to it for you. If you're worried about sensitivity, you can email it.

 

[jereece]

I was curious so I created a local database and report. It works fine in that database. The only difference I can think of is where the data is stored but I am unsure why that would make a difference. I will continue looking at this tomorrow.

 

[pbaldy]

I use SQL Server most of the time. The original test I posted used an Access table, but I just tested on a form based on a SQL Server table, and again it worked fine. You might double check the data type of the field in SQL Server. If it's Char, there may be padded spaces at the end of the field. Of course, if that was the issue, I wouldn't expect the first test to be met, which appears to be happening in your screen shot.

 

[jereece]

The Data Type is Text. In the query that the report is based on, I am using * as a wild card with no problem. I am using Like "*1*" in the query. Attached is another image of my report in design view showing the field properties and the conditional formatting properties. Do you see anything incorrect?

Again, thanks for the help.

Jim