Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional formatting gone wrong! (2003)

    Hi,

    I am try to use conditional formatting to highlight records based on there last update time.

    The field displays a timestamp plus notes of last edit. I am using the following criteria

    If value is less than or equal to Date()-15 Format background Yellow
    If value is less than or equal to Date()-31 Format background Orange
    If value is less than or equal to Date()-61 Format background Red

    Everything else GREEN

    I have a record with a timestamp of 16/08/08 I expected this to go Yellow but it goes Orange. I am obviously not thinking this through correctly but I can't seem to see the problem Date()-16 = 09/08/08 so as my timestamp is greater I thought I would see the yellow background

    Many thanks

    Kevin
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional formatting gone wrong! (2003)

    What kind of field is the timestamp field in the underlying table? Text, number, date/time?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting gone wrong! (2003)

    date/time

    Thanks
    Regards
    Gerbil (AKA Kevin)

  4. #4
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting gone wrong! (2003)

    Hi Hans,

    Thanks for the reply, I will try to upload something, but by the nature of the data I am going to have to be very carful. Its far more than my jobs worth.

    Thanks
    Regards
    Gerbil (AKA Kevin)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional formatting gone wrong! (2003)

    In that case, I don't understand why it goes wrong. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Conditional formatting gone wrong! (2003)

    Have a look at the attachment and see if it does what you want.

    I am confused by the statement: "The field displays a timestamp plus notes of last edit. "

    Do you have the notes in the same field as the date? It can't be a date field if you do that.
    Attached Files Attached Files
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting gone wrong! (2003)

    Thanks for the attachment, will try it out today.

    The underlying table stores 5 fields of data, two of which are TimeStamp (Date/Time) and Notes (Text).

    On the form I a using an unbound textbox, and vba code to lookup the value in 'timestamp' & 'Notes' WHERE CRN = CRN, so the text box displays two fields in one box

    Many thanks

    Kevin

    HANS: Your query idea worked perfect thank you.
    Regards
    Gerbil (AKA Kevin)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional formatting gone wrong! (2003)

    Are you trying to use conditional formatting on the unbound text box that displays the timestamp and notes? If so, you should use conditional formatting using an expression that compares the value of the timestamp field to the current date. If you use the value of the unbound text box, the date part will be seen as a text value, and hence the date comparison will be alphanumeric...

  9. #9
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting gone wrong! (2003)

    That is exactly what I am doing, thank you it now works fine (I think)

    Thanks Hans
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •