Conditional Formatting for Dates - nothing seems to work

[Chris]

Hello there,

I'm not a totally new access user but I've never done any coding. I've just
updated to access 2003 at my workplace and with all the new things I
discovered 'conditional formatting'. My database uses dates and numbers
relating to these dates and I was overjoyed to see that I could set up
different colours for the number values based on a certain criteria. I
thought doing the same for dates would be just as wonderful but something is
really wrong.

First I simply put in the date of the conditional formatting windows
relevant field. Didn't work. Then did all sorts of trick, used access' help,
I've browsed through these topics and tried some of the suggestions given to
other uers. I tried setting the formatting to null value (of course it didn't
work), setting it to Date(), used one of the Dateserial() functions
suggested, used a specific date. Nothing. I have checked that field
properties is set to date, I went back and confirmed with myself that the
original table's corresponding field is surely set to date format as well.
I've tried converting the formatting's date to a number, just in case. Didn't
work. I've run out of ideas and can't comprehend how come the formatting
works for the number field but not the date one.

What I want to achieve is some colouring change when the date field's value
is less than this year's 1st of January in the form. I did try the
dateserial() function suggested in another topic but even that one didn't
work. I would be just as happy if the field got coloured when the value is
less then a year to date - will not make any difference which condition is
used in the form, but I'm starting to think that the problem's cause may lie
somewhere else.

Sorry about being a bit longwinded just wanted to let people know all the
options I've tried to no effect.

Thank you for your help. Chris

 

[BruceM]

With the form or report design view, click on the text box to select it.
Click Format >> Conditional Formatting. Select Field Value Is in the
leftmost box), and Less Than in the middle box. In the rightmost box enter:
DateSerial(Year(Date()),1,1)
Select a fill color or font color, as you prefer.
Help has more information about DateSerial, if you want to know more about
what is going on there.

 

[Steve Schapel]

Chris,

Field value is | less than | #1/1/2008#

 

[Steve Schapel]

Chris,

The functions you mentioned should also work...

Field value is | less than | DateSerial(Year(Date()),1,1)
Field value is | less than | DateAdd("yyyy",-1,Date())

 

[Chris]

Thank you guys, both of you.

I am actually gutted. I HAD tried all of the things you just suggested and
at the time it would not work for anything. Now it does, so maybe something
WAS wrong with the computer or the program. Anyway, I feel a bit silly now,
but at least it works. ALL of the suggested formulas do. Thank you so much!

Chris