If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formatting for Dates - nothing seems to work
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 |
#2
|
|||
|
|||
Conditional Formatting for Dates - nothing seems to work
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. "Chris" wrote in message ... 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 |
#3
|
|||
|
|||
Conditional Formatting for Dates - nothing seems to work
Chris,
Field value is | less than | #1/1/2008# -- Steve Schapel, Microsoft Access MVP Chris wrote: 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 |
#4
|
|||
|
|||
Conditional Formatting for Dates - nothing seems to work
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()) -- Steve Schapel, Microsoft Access MVP Chris wrote: 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 |
#5
|
|||
|
|||
Conditional Formatting for Dates - nothing seems to work
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 |
Thread Tools | |
Display Modes | |
|
|