CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,900

    [RESOLVED] SQL Query Problem

    I have an SQL Query which lists records from a Table which contains Invoices and Credits.
    The problem is that the Values are Positive for both Invoices and Credits - Just that the Document Type Shows which is which.
    My problem is that I want to sum the Total Value by ADDING Invoice Vales and SUBTRACTING Credit values.

    Is it possible to somehow change the Amount to be Negative in an SQL Statement (Based on a Column value containing the word "Credit")

    This is my current query

    Code:
    Select [Territory] , [OBatch], [DDocType], [DDocNo], [DDocDate], [Account], [Company], [Value]  from [OrderArchiveIndex] ORDER BY [Territory] ASC , [Account] ASC
    DDocType contains "Invoice" or "Credit"
    Value contains a Positive Value always - would like this to become Negative if DDOCType = "Credit"

    Thanks for any ideas

    PS - Found the Code Tags - they dont seem to be part of "QUICK REPLY"

  2. #2
    Join Date
    Jul 2005
    Posts
    1,083

    Re: SQL Query Problem

    You could use a "Case When" in the "Select" to ask for the record type
    and use a "-" (minus sign) before the field to convert to negative
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  3. #3
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,900

    Re: SQL Query Problem

    I have tried making this work using Case but keep getting syntax errors

    Does Case work with an SQL Statement on an Access database or do you need to be running T-SQL (with a SQL Server Database) ?

    I will persevere in the mean time,

    Thanks

  4. #4
    Join Date
    Jul 2005
    Posts
    1,083

    Re: SQL Query Problem

    With MS Access you could use IIF
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  5. #5
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,900

    Re: SQL Query Problem

    Thanks for the guidance - the IIF Statement worked perfectly

    Here is the final Query which works (Thanks)


    Select [Salesman], [Account], [Company], [DDocType], [DDocNo], [DDocDate], [DDocStatus], [DDocOPID], [OOrderV7], [OOrderV9], IIF( [DDoctype] = 'Credit', [OOrderV12] * -1, [OOrderV12]) as InvoiceTotal from [OrderArchiveIndex] ORDER BY [DDocNo] ASC

  6. #6
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,900

    Re: SQL Query Problem

    Thanks for the guidance - the IIF Statement worked perfectly

    Here is the final Query which works (Thanks)


    Code:
    Select [Salesman], [Account], [Company], [DDocType], [DDocNo], [DDocDate], [DDocStatus], [DDocOPID], [OOrderV7], [OOrderV9], IIF( [DDoctype] = 'Credit', [OOrderV12] * -1,   [OOrderV12]) as InvoiceTotal from [OrderArchiveIndex] ORDER BY [DDocNo] ASC

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured