Conditional formatting with SQL Select from another table

[MagnusS]

Hi,

I trying to set the font color in a textbox using this "Expression is":
[txtProdAvailableDate]<=Date() And [ProdAvailableChk]=-1 And
[txtProdAvailableDate]<(SELECT [tblLog].[ProdAvCheckedDate] FROM [tblLog]
WHERE [ID]=Forms![frmTracing].[txtID]).
This doesn't give any error message but neither does it change the font color.

I have this expression working:
[txtProdAvailableDate]<=Date() And [ProdAvailableChk]= 0

Any suggestion on how to solve the string where I use the SELECT statement?

Cheers,
/Mag

 

[Douglas J. Steele]

You can't use SELECT statements like that. Try using DLookup:

[txtProdAvailableDate]<DLookup("[ProdAvCheckedDate]", "[tblLog]", "[ID]=" &
Forms![frmTracing].[txtID])

 

[Daryl S]

MagnusS -

You might try putting a hidden field on your form, and setting the value to
be the results of your SQL statement. Then in your formatting expression,
use the value of this hidden field instead of the SQL statement.