# Thread: Calculated field (A2K SP3)

1. ## 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. ## 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. ## Re: Calculated field (A2K SP3)

Thanks. Your solution works great.

Tom

4. ## 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. ## 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
•