Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello, hoping you can help with this one. I have connected to an ODBC database and linked to the tables and data that I need for a special report. I am pulling the total amounts of all issued invoices, but the problem lies in separating the debit invoices from the credit invoices. The amounts are all listed as positive numbers in one field, but to identify them there is another field that holds the invoice number starting with either a "CN" or "IN". In my query, I have set up a field to show the following:
    Type: Left([IDINVC],2). I now need a formula or an iif statement that will separate the corresponding amounts based upon the type into another 2 distinct fields or else turn a positive into a negative. Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    You can change the sign like this:

    ModifiedAmount: [Amount]*IIf(Left([IDINVC],2)="CN",-1,1)

    where Amount is the name of the field containing the amount of the invoice.

    If you prefer to create two separate fields:

    CNAmount: IIf(Left([IDINVC],2)="CN",[Amount],Null)

    INAmount: IIf(Left([IDINVC],2)="IN",[Amount],Null)

    where again Amount is the name of the field containing the amount of the invoice.

  3. #3
    New Lounger
    Join Date
    Jun 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you! Works great.

Posting Permissions

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