Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2014
    Posts
    6

    Unanswered: Conditional formatting issue

    Hi

    I am trying to set conditional formatting on a field within a subform in a form. I want it to display red when it has passed todays date so have used Value <Date () and also to display blue when the date is within 30 days so have used Value <Date()+30. This has worked fine so far.

    However, I am tryign to use it on a field which is not set as a Date/Time field and when I apply the same date formatting it is setting all entries in that fied as red. Any suggestions?

    Thanks

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I haven't tried using conditional formatting on Access for a long time, but have you tried converting the value to a date before applying the conditional check?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Apr 2014
    Posts
    6
    Em, no and I don't know how to do that? Any help much appreciated!

    Thanks

  4. #4
    Join Date
    Apr 2014
    Posts
    6
    Em, no. I have no idea how to do that - any help greatly appreciated!!

    Thanks

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the cdate function
    and isdate()
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2014
    Posts
    6
    That sounds like it would work but I have never used any coding before in Access - where and how do I use this?

    Thank you!

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You'd use it the same as before, simply converting the value to date:

    CDate([PseudodateControl]) <Date ()

    replacing PseudodateControl with the actual name of the Control in question.

    The question that hasn't been asked, and should be, is why are you storing a date value in a Field that is not defined as a Date/Time Field?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    they are both vba functions
    use them as you would use any vba function
    if in doubt have a look at the help file for those functions
    or, dare i mention it, do a google for those functions and/or Access date time functions
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2014
    Posts
    6
    Thank you both for your reply. WIll go try it.

    We are storign dates in a field that for some people we have the date of their certificate expiry in which case a date is entered but for some personnel we don't have the date so just record a yes to show that they have that certificate.

    Thanks

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    This will make using CDate() impossible. If you use CDate() against a Text value, i.e. 'Yes,' it will pop a Runtime Error 13, Datatype Mismatch. A better approach would probably be to have the Field defined as Date/Time and giving it a Default Value that is way, way in the future, say 1/1/2100. Then, when entering a New Record, if there is no expiration date, the date will default to 1/1/2100 and everyone will understand that this is the same as your current 'yes.' If you need to run a query or report for employees whose certifications are not current, these employees will not be included, which is what you want.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Apr 2014
    Posts
    6
    OK, thanks that makes sense.

    Thank you for taking the time to reply again.

Posting Permissions

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