Page 2 of 2 FirstFirst 1 2
Results 11 to 15 of 15

Thread: Change positive number to negative for some items in a field

  1. #11
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,738
    Articles
    76
    Blog Entries
    14


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Can you share what program it is? (Who is the vendor?) That may help...
    Ken Puls, CMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #12
    Micronet Systems Australia
    To get the currency balance on a debtor report the following print logic is used. This may give some insight to the programming used.
    Print Logic "" != CURNO(5)


    Accumulate if DTRN_EXCH == CURNO(5)

  3. #13
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,738
    Articles
    76
    Blog Entries
    14
    Unfortunately, not really.

    I'm seeing that there's a couple of options. You could email them and ask or you could use your original query, then run an Excel formula down to convert it in the next column. Copy, pastespecial --> values and you can delete the original column. Honestly, that may be the fastest way.
    Ken Puls, CMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #14
    Many thanks, Ken for all assistance given and for entertaining my request in the first place. Already I have learnt a fair bit through your replies. Have read your powerpivot blogs as well so thank you again. Have a great weekend

  5. #15
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0


    Register for a FREE account, and/
    or Log in to avoid these ads!

    kariaheart

    It may be a little late here, but what the heck... Why not just use a plain old CASE statement. I know for a fact the below syntax works in SQL Server, DB2, and Oracle, but I would think it should work in any database supporing ANSI standard SQL. It does not work in Access, and Excel might not like it either.

    Code:
    SELECT 
     Inventory_Movement_File.IM_ITMNO, 
     Inventory_Movement_File.IM_TYPE, 
     Inventory_Movement_File.IM_DATE,
     CASE WHEN Inventory_Movement_File.IM_TYPE = '71' 
          THEN Inventory_Movement_File.IM_QTY*-1
          ELSE Inventory_Movement_File.IM_QTY AS IM_QTY
      END
     FROM OAUSER.Inventory_Movement_File Inventory_Movement_File
    WHERE (Inventory_Movement_File.IM_DATE>=? And Inventory_Movement_File.IM_DATE<=?) 
      AND (Inventory_Movement_File.IM_QTY<>0)
    The only thing you might need to do is convert Inventory_Movement_File.IM_QTY to integer if it is indeed stored as...GASP...char.

    Greg

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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