Conditional Formating

[Marc]

I have seven text boxes with calculations in them on my form. I'm trying to
get the highest value out of the seven to turn red. I used the conditioanal
formating feature and it doesn't work for the "greater than" function. So I
tried the "less than" function and that worked. So I don't understand why the
"greater than wouldn't work. Any Ideas there. Thanks

 

[Duane Hookom]

You should provide your exact expression that you are using. You can see it
and we can't.

 

[Allen Browne]

What was the expression you used to compare all 7 text boxes, Marc? It must
have contained several nested IIf()s.

Copy the MaxOfList() function from here:
MinOfList(), MaxOfList(): Get the min/max of a list of values
at:
http://allenbrowne.com/func-09.html

Assuming fields named F1, F2, ... F7, you could then put this into Condition
1 of the Conditional Formatting (Format menu) or F1:
Expression ... [F1] = MaxOfList([F1], [F2], [F3], [F4],[F5],
[F6],[F7])

 

[Marc]

This is the expression in the text boxes:

=IIf([Finished Date Man BR by QA]-[Packaging Date]<0,0,[Finished Date Man BR
by QA]-[Packaging Date])

Thanks

What was the expression you used to compare all 7 text boxes, Marc? It must
have contained several nested IIf()s.

Copy the MaxOfList() function from here:
MinOfList(), MaxOfList(): Get the min/max of a list of values
at:
http://allenbrowne.com/func-09.html

Assuming fields named F1, F2, ... F7, you could then put this into Condition
1 of the Conditional Formatting (Format menu) or F1:
Expression ... [F1] = MaxOfList([F1], [F2], [F3], [F4],[F5],
[F6],[F7])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have seven text boxes with calculations in them on my form. I'm trying to
get the highest value out of the seven to turn red. I used the
conditioanal
formating feature and it doesn't work for the "greater than" function. So
I
tried the "less than" function and that worked. So I don't understand why
the
"greater than wouldn't work. Any Ideas there. Thanks

 

[Marc]

Ok in Conditioning Format box, I chose expression and wrote this

[BR1]=MaxOfList([BR1],[BR2],[BR3],[BR4],[BR5],[BR6],[BR7])

Let me know if that's right because I still didn't get it to work.

Thank you for your help!

What was the expression you used to compare all 7 text boxes, Marc? It must
have contained several nested IIf()s.

Copy the MaxOfList() function from here:
MinOfList(), MaxOfList(): Get the min/max of a list of values
at:
http://allenbrowne.com/func-09.html

Assuming fields named F1, F2, ... F7, you could then put this into Condition
1 of the Conditional Formatting (Format menu) or F1:
Expression ... [F1] = MaxOfList([F1], [F2], [F3], [F4],[F5],
[F6],[F7])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have seven text boxes with calculations in them on my form. I'm trying to
get the highest value out of the seven to turn red. I used the
conditioanal
formating feature and it doesn't work for the "greater than" function. So
I
tried the "less than" function and that worked. So I don't understand why
the
"greater than wouldn't work. Any Ideas there. Thanks

 

[Marc]

Do you thinkg my IF statements in the text fields have something to do with
it not working? The If Statement is =IIf([Micro Report Received
Date]-[Packaging Date]<0,0,[Micro Report Received Date]-[Packaging Date])
Thanks

 

[Allen Browne]

Marc, I am assuming here that:
- all 7 fields are of type Number (not Text), and
- you want which ever one is the highest number to turn red.

You will therefore need conditional formatting on all 7 of them.
The expression you posted looks like the right thing for BR1.
The others will be similar - just changing the first part to match the name
of the box you are formatting. So the CF expression for BR2 will be:
[BR2]=MaxOfList([BR1],[BR2],[BR3],[BR4],[BR5],[BR6],[BR7])

Just to check that you have the function working correctly, open the
Immediate Window (press Ctrl+G), and enter?
? MaxOfList(2,4,7,1)
and you should get the response 7.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok in Conditioning Format box, I chose expression and wrote this

[BR1]=MaxOfList([BR1],[BR2],[BR3],[BR4],[BR5],[BR6],[BR7])

Let me know if that's right because I still didn't get it to work.

Thank you for your help!

What was the expression you used to compare all 7 text boxes, Marc? It
must
have contained several nested IIf()s.

Copy the MaxOfList() function from here:
MinOfList(), MaxOfList(): Get the min/max of a list of values
at:
http://allenbrowne.com/func-09.html

Assuming fields named F1, F2, ... F7, you could then put this into
Condition
1 of the Conditional Formatting (Format menu) or F1:
Expression ... [F1] = MaxOfList([F1], [F2], [F3], [F4],[F5],
[F6],[F7])

I have seven text boxes with calculations in them on my form. I'm trying
to
get the highest value out of the seven to turn red. I used the
conditioanal
formating feature and it doesn't work for the "greater than" function.
So
I
tried the "less than" function and that worked. So I don't understand
why
the
"greater than wouldn't work. Any Ideas there. Thanks

 

[Marc]

I went to intermediate window and typed as you told me to: ? MaxOfList
(2,4,7,1)
I get a compile error sub or function not defined. So if the function isn't
working correctly how would I correct it?

Thanks

Marc, I am assuming here that:
- all 7 fields are of type Number (not Text), and
- you want which ever one is the highest number to turn red.

You will therefore need conditional formatting on all 7 of them.
The expression you posted looks like the right thing for BR1.
The others will be similar - just changing the first part to match the name
of the box you are formatting. So the CF expression for BR2 will be:
[BR2]=MaxOfList([BR1],[BR2],[BR3],[BR4],[BR5],[BR6],[BR7])

Just to check that you have the function working correctly, open the
Immediate Window (press Ctrl+G), and enter?
? MaxOfList(2,4,7,1)
and you should get the response 7.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok in Conditioning Format box, I chose expression and wrote this

[BR1]=MaxOfList([BR1],[BR2],[BR3],[BR4],[BR5],[BR6],[BR7])

Let me know if that's right because I still didn't get it to work.

Thank you for your help!

What was the expression you used to compare all 7 text boxes, Marc? It
must
have contained several nested IIf()s.

Copy the MaxOfList() function from here:
MinOfList(), MaxOfList(): Get the min/max of a list of values
at:
http://allenbrowne.com/func-09.html

Assuming fields named F1, F2, ... F7, you could then put this into
Condition
1 of the Conditional Formatting (Format menu) or F1:
Expression ... [F1] = MaxOfList([F1], [F2], [F3], [F4],[F5],
[F6],[F7])

I have seven text boxes with calculations in them on my form. I'm trying
to
get the highest value out of the seven to turn red. I used the
conditioanal
formating feature and it doesn't work for the "greater than" function.
So
I
tried the "less than" function and that worked. So I don't understand
why
the
"greater than wouldn't work. Any Ideas there. Thanks

 

[Allen Browne]

It sounds like VBA can't find the function.

Where did you type it in?

In the Database window, choose the Modules tab.
Click New.
Access opens a code window.
Paste it in there, below the Option statement(s).
Save it with a name such as Module1.
Choose Compile on the Debug menu, to verify Access understands it.

There may be a problem in another part of your database that is preventing
the code from compiling.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I went to intermediate window and typed as you told me to: ? MaxOfList
(2,4,7,1)
I get a compile error sub or function not defined. So if the function
isn't
working correctly how would I correct it?

Thanks

Marc, I am assuming here that:
- all 7 fields are of type Number (not Text), and
- you want which ever one is the highest number to turn red.

You will therefore need conditional formatting on all 7 of them.
The expression you posted looks like the right thing for BR1.
The others will be similar - just changing the first part to match the
name
of the box you are formatting. So the CF expression for BR2 will be:
[BR2]=MaxOfList([BR1],[BR2],[BR3],[BR4],[BR5],[BR6],[BR7])

Just to check that you have the function working correctly, open the
Immediate Window (press Ctrl+G), and enter?
? MaxOfList(2,4,7,1)
and you should get the response 7.

Ok in Conditioning Format box, I chose expression and wrote this

[BR1]=MaxOfList([BR1],[BR2],[BR3],[BR4],[BR5],[BR6],[BR7])

Let me know if that's right because I still didn't get it to work.

Thank you for your help!

:

What was the expression you used to compare all 7 text boxes, Marc? It
must
have contained several nested IIf()s.

Copy the MaxOfList() function from here:
MinOfList(), MaxOfList(): Get the min/max of a list of values
at:
http://allenbrowne.com/func-09.html

Assuming fields named F1, F2, ... F7, you could then put this into
Condition
1 of the Conditional Formatting (Format menu) or F1:
Expression ... [F1] = MaxOfList([F1], [F2], [F3], [F4],[F5],
[F6],[F7])

I have seven text boxes with calculations in them on my form. I'm
trying
to
get the highest value out of the seven to turn red. I used the
conditioanal
formating feature and it doesn't work for the "greater than"
function.
So
I
tried the "less than" function and that worked. So I don't
understand
why
the
"greater than wouldn't work. Any Ideas there. Thanks