Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple Access formula acting up (Access2003)

    I am back to Access Reporting and something I had definitely forgotten like WHY in a world would Access freak out at me for writing this [GRADE_FROM] & " - " & [GRADE_TO] in a property/control source of the field?
    I do have both of these firelds in my query! What is wrong with my 'formula'?
    It says MS can't find the field you reffering to in your formula.
    Thanks

    P.S. Can this be happening because I just saw Grade_TO is empty some places?
    If it is then I would need to write something like
    (IIF(IsNull([GRADE_FROM], [GRADE_TO], ([GRADE_FROM] & " - " & [GRADE_TO]) OR IIF(ISNUll([GRADE_TO] , [GRADE_FROM], ([GRADE_FROM] & " - " & [GRADE_TO]) )

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Access formula acting up (Access2003)

    Your formula should work even if one of the fields is null (tested in 2000).

    Do you have your "=" in front of your formula? If I do not include the "=", my field results in a #Name?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Access formula acting up (Access2003)

    Hey! You happened to be absolutely correct BUT if 1 field is null it can result instead of 5-7
    -7
    or
    5-
    wouldn't that be?

    I've changed it - no luck still!
    =(IIf(IsNull([GRADE_FROM]),[GRADE_TO],[GRADE_FROM])) & " - " & (IIf(IsNull([GRADE_TO]),[GRADE_FROM],[GRADE_TO]))

    Thanks

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Access formula acting up (Access2003)

    True, the dash would be a problem.
    I would use:
    =[grade_from] & IIf(IsNull([grade_from]) Or IsNull([grade_to]),""," - ") & [grade_to]

    The dash is only inserted when both fields are not null

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Access formula acting up (Access2003)

    You wanna laugh?
    I took out = and it works a charm!
    Thanks and please, comment if you wish!

Posting Permissions

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