Change positive number to negative for some items in a field

[Ken Puls]

Can you share what program it is? (Who is the vendor?) That may help...

[kariaheart]

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)

[Ken Puls]

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.

[kariaheart]

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

[gsnidow]

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.

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