Is that control bound to anything (I think not)? If so, you can't enter an expression into it. If not, what is it? A textbox? If so, you'll need a combination of a DMax expression and Conditional Formatting. You can't enter a sql statement as a control source for a textbox, but you can use DMax with an expression that returns the last date value, or nothing if nothing satisfies the criteria part of the lookup. See the use of DMax here http://www.techonthenet.com/access/f...omain/dmax.php
Once you have that, use conditional formatting to format the colors you want for the textbox background or font. Will any of this keep the user from ordering anything that shows up in red, or does it really just warn someone who may order it anyway? I see by your posted image that you use spaces in your field names (bad practice). Maybe you should take a look at some db object naming recommendations.
http://www.access-programmers.co.uk/...d.php?t=225837
http://access.mvps.org/access/general/gen0012.htm
http://allenbrowne.com/AppIssueBadWord.html
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.