Conditional Formatting (2000)

Topic

New Reply

I have a frmDispatch (continuous) for the dispatchers that displays a text box (Contol Source, Combo Box from frmOrderHistory) showing the type of equipment assigned to the load. There are 23 different types of equipment in the ComboBox and there is really no reason to color the text box on the frmDispatch for every type of equipment. However, a common error by the dispatchers is when a particular type of equipment is assigned without noting the “class” of the equipment. Knowing that there are only three options within “Conditional Formatting”, I went to the table where the ComboBox gets its equipment types and amended the entries to include the word “only” in their descriptive names. My “logic” was that I could use “Conditional Formatting” to look only for the “only” text in the box. So much for logic. What I would like to do is color the text box red, and the font color to white, for any equipment type that includes the word “only” in the text.

I tried using the “Expression Is” statement with the following:

Like”&only&”

I also tried this from the “Field Value Is” statement. In both cases I got a syntax error.

I’m just beginning to learn the basics of VBA and would actually prefer to write the conditional statement rather than use the “wizard”, but I’m afraid I’m over my head. Each challenge is a learnng experiance, so I’d really like to figure out code to accomplish this seemingly simple task.

Reply | Quote

Replies

I just tried this out and found that if I use the “Expression is” option and put this expression in the box it worked:
IIf([firstname] Like “*” & “only” & “*”,True,False)

In my case I was using a firstname field

Reply | Quote

In fact all you need is:
[firstname] Like “*” & “only” & “*”
in the box next to “Expression is”

Reply | Quote

I just KNEW there had to be a way! Actually, I had given up after littering my office floor with every book on Access I own, and several pieces of wadded up scratch paper. I finally quit when I read that the “&” sign could not be used in a conditional statement (but that was a book on Access 97). So, either you’ve made a liar out of them or there is a difference between ’97 and 2000. At any rate, you have not only solved the immediate problem rather famously, but you have taught me a rather valuable lesson – don’t always believe what you read, and there are people at Woody’s who have far more talent than I might have patience.

This idea of using a naming “convention” (i.e. the “only” statement) in the table the combo box is built on, along with your generous Expression Is contribution, opens up a whole new way of thinking for me in other applications as well. I am, indeed, in your debt.

Thanks!

Reply | Quote

Access97 didn’t have conditional formatting so whatever was meant by the statement that “&” can’t be used in a conditional statement, it wasn’t talking about this.

Glad this solved your problem.

Reply | Quote