Expression for conditional formatting in form

[Kai90]

Hello,

I would like to conditionally format a text box on a form based on if the value entered is smaller or greater than a value on a table.


The table looks like this:

[Field1] [Field2] [Field3]

15______-6_______5
30______-2_______3
45______-1_______2
60_______1_______9

My conditional format would check the following: If value in text box ist smaller than the value in [Field2] on the table WHERE [FIELD1]=15, then color it red.


I am currently trying to use the conditional formatting on the text field with the following expression:
IIf([FORM1].[TEXTFIELD1]<[TABLE1].[FIELD2].[&FIELD1=15],True)

then it is supposed to color it red. However, something is missing or wrong in my expression.

Any ideas?
Thank you guys so much,

Kai

 

[boblarson]

The fields need to be in the underlying query of the form.

And, you would refer to them in the conditional format by setting it to EXPRESSION IS and then (I am assuming that you were talking about field 2 comparing to field 3 where the first field is 15):

[FIELD2]< [FIELD3] AND [FIELD1] = 15

 

[Kai90]

Thank you so much for the answer.

My problem though is, that these fields (FIELD1,FIELD2 and FIELD3) are not in the underlying query of the form, but on a separate table ( a range table, so to speak) that is not connected to the form.

Is there no way to refer to this table? I found a microsoft.support page where they can look up and compare data to an external table using the dlookup funciton.

I am just trying to compare a value on the form (TEXTBOX1) to a value on the table [FIELD2] where a certain condition is met [FIELD1]=15.

 

[boblarson]

Thank you so much for the answer.

My problem though is, that these fields (FIELD1,FIELD2 and FIELD3) are not in the underlying query of the form, but on a separate table ( a range table, so to speak) that is not connected to the form.

Is there no way to refer to this table? I found a microsoft.support page where they can look up and compare data to an external table using the dlookup funciton.

I am just trying to compare a value on the form (TEXTBOX1) to a value on the table [FIELD2] where a certain condition is met [FIELD1]=15.

It may be possible to use a Dlookup in the conditional formatting but you can't just refer to the table like you had in the first post.

It might work if you have

Expression Is:

[TextBox1] < Nz(DLookup("Field2", "Table1", "[Field1]=15), 0)

But I'm not sure as a DLookup will return the first value that matches and if you have more field1 values of 15 then it wouldn't work. And also since tables aren't ordered sets you would need to provide a query with an OrderBy clause, instead of just referring to the table in the DLookup, so you get consistent results.

 

[Kai90]

Ok, I see where you are going.

Unfortunately, it is not working right now, so I created a query with the table's information in it. The "15" only occurs once in the query, so there should be no confusion on that part.

The conditional formatting is accepting the expression, but nothing happens...

 

[boblarson]

The conditional formatting is accepting the expression, but nothing happens...

Did you make sure to change the format in the conditional formatting so it would do something?

 

[Kai90]

Yes, it is supposed to color the the box red if the condition were true.
I entered a value that is smaller than the looked up value, but nothing happens...

 

[boblarson]

Yes, it is supposed to color the the box red if the condition were true.
I entered a value that is smaller than the looked up value, but nothing happens...

Did you make sure you have a control to which you can move so that the control can be updated? If you remain in the control it won't be updated and hence it won't have a value that can be compared against.

 

[Kai90]

Yes, there are about 20 different text boxes on my form. Once I entered a value in the first control (the one where I am trying to apply this conditional formatting), I hit ENTER or TAB and move to the next control....

 

[boblarson]

It may be that Conditional Formatting doesn't work with unbound controls. I can't remember off hand.