none
Highlighting text in a date field using conditional formatting RRS feed

  • Question

  • Hi

    For certain fields I have a default value of "Awaiting" and on the form or any reports where this is present, conditional formatting shows "Awaiting" in bold red.  This is on fields whose data type is short text.

    I also have a similar thing going on for certain date fields where the format of the field is set as dd/mm/yyyy;;;"Awaiting".  This works fine and allows the word Awaiting to be placed in a date field.

    The problem I have is that the conditional formatting does not work for the date field on either the form or any reports.  I think I get it that it may be because this is text in a date field, but I cannot think of a way around it.

    Can anybody help please?

    Thanks.


    Chris | UK

    Tuesday, May 31, 2016 10:46 AM

Answers

  • Give this a try...

    In the conditional formatting popup, select 'Expression Is'.

    Try any of the following in the expression textbox.  Make sure to include the brackets.  For the first two, use the name of the textbox on your form.  For the last, use the name of the field in the underlying table.

    instr(1,[txtYourDateTextBox],"Awaiting") > 0
    
    [txtYourDateTextBox] = "Awaiting"
    
    
    NZ([YourDateField], "Awaiting") = "Awaiting"


    Miriam Bizup Access MVP

    • Marked as answer by ChrisParkin Tuesday, May 31, 2016 12:22 PM
    Tuesday, May 31, 2016 11:15 AM

All replies

  • Give this a try...

    In the conditional formatting popup, select 'Expression Is'.

    Try any of the following in the expression textbox.  Make sure to include the brackets.  For the first two, use the name of the textbox on your form.  For the last, use the name of the field in the underlying table.

    instr(1,[txtYourDateTextBox],"Awaiting") > 0
    
    [txtYourDateTextBox] = "Awaiting"
    
    
    NZ([YourDateField], "Awaiting") = "Awaiting"


    Miriam Bizup Access MVP

    • Marked as answer by ChrisParkin Tuesday, May 31, 2016 12:22 PM
    Tuesday, May 31, 2016 11:15 AM
  • Hi Miriam

    I tried your last one first and it works perfectly for both the form and the reports.

    Thanks very much.


    Chris | UK

    Tuesday, May 31, 2016 12:21 PM
  • Glad to help out - good luck with your project!

    Miriam Bizup Access MVP

    Tuesday, May 31, 2016 12:25 PM