Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Location
    Fort Worth, TX
    Posts
    43

    Unanswered: Conditional Formatting Question

    Ok, I have a continuous form that tracks certain points along a route of a shipment and want to have points standout when there is no date and a cerain amount of time has passed from the point before.

    Example
    field name--Departure--ArrivalPort------ArrivalDest
    data--------8/15-------8/20-----------Null

    I want the field [ArrivalDest] to be Red if it is null and the date in [ArrivalPort] is more than 5 days ago

    what is the code I need to put in the Conditional Formatting

    I am thinking it is something like
    Condition 1
    [ArrivalPort]>=Date()-5 & [ArrivalDest] is Null

    But I may be way off

    Help!

  2. #2
    Join Date
    Aug 2010
    Location
    Hendersonville
    Posts
    1

    You were sooooo close

    Hi there,
    Instead of putting the '&' sign in the condition, you would use 'And"

    [ArrivalPort]>=Date()-5 And [ArrivalDest] is Null

    That should do it. Hope this helps.

  3. #3
    Join Date
    Sep 2010
    Location
    USA
    Posts
    1
    Hello, Thank you so much for the advice. I guess there is a difference between 'and' and '&'

  4. #4
    Join Date
    Sep 2010
    Location
    USA
    Posts
    1
    I think the '&' sign is used within a single formula where as 'And' is used to combine two difference formulas.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In Access VBA the ampersand (&) is used to concatenate strings.

    Also in VBA

    [ArrivalDest] is Null

    needs to be

    IsNull([ArrivalDest])

    So you'd use

    Expression Is

    [ArrivalPort]>=(Date()-5) And IsNull([ArrivalDest])

    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

  6. #6
    Join Date
    Oct 2010
    Location
    USA
    Posts
    1
    thanks for the info. but I think in Access query formulas, the 'Is Null' comes after the field name

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Actually IsNull() and Is Null can be used in Access SQL statements, but the subject of the thead is Conditional Formatting.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

Posting Permissions

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