Conditional Formatting a Text Box on a Report

[Holly_Associated]

Hi All,
I'm about tearing my hair out so thought I would ask the experts. It's something I've done before but for the life of me I don't know why it isn't working this time...

I have a text box on a report from the Control Source CSCS Expiry Date.
The format in the table and query and report is Short Date.
The Text Format is Plain Text.
I have named the text box txtCSCSExpiryDate.
I would like to make the background red when the date is 01/04/2013.
I have ensured the text box back style is normal.

I am using expression
[txtCSCSExpiryDate]=#01/04/2013#
and in case it was the Control Source:
[CSCS Expiry Date]=#01/04/2013#
I also tried:
[CSCS Expiry Date]<Date()
[txtCSCSExpiryDate]<Date()

I applied the formatting each time, tried closing and re-opening the database. Changed the background format colour to green instead of red and even restarted my computer and it just stays exactly the same.
Any help much appreciated!

Edit: I've just noticed something else. I have a screen only button on my report header with
DoCmd.OpenReport "All_CSCSExpiry_Rpt" acViewPreview
which has also stopped working. I get an error:
The expression On Click you entered as the event property setting produced the following error: A problem occurred while Employee Details was communicating with the OLE server or ActiveX Control.

Is this linked to my formatting not working?

Oh I've just checked and on my 5 reports all of the print buttons are giving me this error! (I added the button as they are part of a navigation form menu which only shows report view, I am editing in the original report design view though.) They were working yesterday! Is it because I compacted and repaired the database?

 

[CJ_London]

are you using american or uk style dates?

The # defaults to american style so your date is actually 4th January, not 1st April - in which case change to #04/01/2013#

 

[Holly_Associated]

Hi CJ, I didn't know that, thank you
I am using UK and have changed the ending back to <Date() but still no joy with the Control Source or Text Box beginning.

 

[CJ_London]

Here is a link for conditional formatting
http://office.microsoft.com/en-gb/a...al-formatting-HA010208133.aspx#BMexpressionis

Also this one to a similar question
http://www.access-programmers.co.uk/forums/showthread.php?t=249256

so for your conditional formatting to work you need to set:

Expression is - [CSCS Expiry Date]=#04/01/2013#

Also, if [CSCS Expiry Date] can be null then you should wrap it with the nz function

Expression is - nz([CSCS Expiry Date])=#04/01/2013#

The other thing I have noticed can have an effect is the use of date in your field name. Even though it is wrapped in square brackets, I have experienced unexpected results in the past so it is better to remove spaces from your field names.

 

[Holly_Associated]

Hi CJ,
I agree, I built this DB a few months ago when I first started learning Access. It has one large table and no normalisation!
I am now on a mission with a blank database and am following the proper procedures. Up to 8 tables already!
I think with all the errors I was getting I had to admit defeat and build it properly!
Thank you very much for your time and assistance (and the nz tip)