Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculated field (A2K SP3)

    I currently have a calculated field ReceiptNumber:[tblRental]![PropertyID] & Right([tblRental]![ReceiptNumber2],4)

    I need to change the expression so if the 5th number from the right is zero in ReceiptNumber2, then I want the last 4 digits else the last five digits.

    For example, 50010016 = 10016
    100001118 = 1118

    Any assistance would be appreciated.

    Tom

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

    Re: Calculated field (A2K SP3)

    Try this one:
    ReceiptNumber: [tblRental]![PropertyID] & IIf(Left(Right([tblRental]![ReceiptNumber2],5),1)="0",Right([tblRental]![ReceiptNumber2],4),Right([tblRental]![ReceiptNumber2],5))

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated field (A2K SP3)

    Thanks. Your solution works great.

    Tom

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

    Re: Calculated field (A2K SP3)

    Or equivalent but slightly shorter:

    ReceiptNumber: [tblRental]![PropertyID] & Right([tblRental]![ReceiptNumber2],IIf(Left(Right([tblRental]![ReceiptNumber2],5),1)="0",4,5))

    or even

    ReceiptNumber: [tblRental]![PropertyID] & Right([tblRental]![ReceiptNumber2],5+(Left(Right([tblRental]![ReceiptNumber2],5),1)="0"))

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated field (A2K SP3)

    Thanks. It nice to learn different ways to solve the problem.

    Tom

Posting Permissions

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