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

 

[Kai90]

I created an unbound text field with the Dlookup function in the control source. That part worked as supposed to.

The field that I'd like to conditionally format is a bound text field though. It is bound to the underlying table of the form. That is where the entered value is stored.

My whole idea is, that when a user enters a value here, that it looks up the value in the range table (or query) and sees if it falls in a certain range. If it is smaller or larger, then I'd like to color the background of that text box red.

 

[Kai90]

Okay, I finally got it to work:

This is what I used in the EXPRESSION IS line at conditional formatting:

[FORM_TEXTFIELD1]<DLookUp("[QUERY1_FIELD1]","QUERY1","[FIELD2]='15'")

It works for bound and unbound text fields on a form.

Thank you so much Bob for your help and patience,

Greetings,
Kai

 

[Kai90]

Just noticed it cut off my query. Here is how it turned out in the final version:

[Geo 25 dL(s)] Between DLookUp("[Query1Field3]","Query1","[Query1Field2]='15'") And DLookUp("[Query1Field4]","Query1","[Query1Field1]='15'")