VBA Excel SQL Select Statement - Case?

[djmulroney]

I'm trying to create an open items listing, this is still early but having a problem.

I am using Excel 2007, using Microsoft Query via an DSN. I am trying to add a Case Statement to the Select Sql, but its giving me an errror:
[Informix][Informix OBDC Driver][Informix]A syntax error has occurred.

The query (code below) works fine with the following excluded:
CASE ar_type.at_oper WHEN '+' THEN ar_item.ar_amount * 1 WHEN '-' THEN ar_item.ar_amount * -1 End as CorrectAmt

Ultimately I want to sum all the items by ar_item.ar_invoice_nbr and exclude any items that (after rounded to 2 decimal places) exclude any items that total zero. Not sure how to do this yet, so if you do know, Yes Please!!! I'd like to put this into VBA code to create this query, was going to start off with logic first.

I greatly appreciate any help that you have to give, many thanks in advance.

SELECT ar_item.ar_invoice_nbr, ar_item.ar_customer, ar_item.ar_seq_nbr, ar_item.ar_tran_type, ar_item.ar_tran_dt, ar_item.ar_amount, ar_item.ar_due_dt, ar_item.ar_disc_date, ar_item.ar_disc_amt, ar_item.ar_shift_link, ar_type.at_oper, ar_type.at_desc, ar_type.at_gl_db, ar_type.at_gl_cr, ar_type.at_prft_ctr, CASE ar_type.at_oper WHEN '+' THEN ar_item.ar_amount * 1 WHEN '-' THEN ar_item.ar_amount * -1 End as CorrectAmt
FROM ssfactor.ar_item ar_item, ssfactor.ar_type ar_type
WHERE ar_item.ar_tran_type = ar_type.at_type_code AND ((ar_type.at_prft_ctr>=500) AND (ar_item.ar_customer='71180005'))
GROUP BY ar_item.ar_invoice_nbr, ar_item.ar_customer, ar_item.ar_seq_nbr, ar_item.ar_tran_type, ar_item.ar_tran_dt, ar_item.ar_amount, ar_item.ar_due_dt, ar_item.ar_disc_date, ar_item.ar_disc_amt, ar_item.ar_shift_link, ar_type.at_oper, ar_type.at_desc, ar_type.at_gl_db, ar_type.at_gl_cr, ar_type.at_prft_ctr

 

[sunnyland]

Let's say I have the data above in an Access table, [I do not have access to Informix and do not not what it is]

<table style='border: thin solid gray;'><tr style='background: #CFCFCF;'><td>Details</td><td>Amount</td><td>Sign</td></tr><tr style='background: white;'><td>Monday</td><td>$35.00</td><td>+</td></tr><tr style='background: white;'><td>Tuesday</td><td>$49.00</td><td>+</td></tr><tr style='background: white;'><td>Wednesday</td><td>$5.00</td><td>-</td></tr><tr style='background: white;'><td>Thurday</td><td>$65.00</td><td>+</td></tr><tr style='background: white;'><td>Monday</td><td>$25.00</td><td>-</td></tr><tr style='background: white;'><td>Tuesday</td><td>$36.00</td><td>+</td></tr><tr style='background: white;'><td>Wednesday</td><td>$20.00</td><td>-</td></tr><tr style='background: white;'><td>Wednesday</td><td>$69.00</td><td>+</td></tr></table>

to get these results by query:

<table style='border: thin solid gray;'><tr style='background: #CFCFCF;'><td>Details</td><td>Amount</td><td>Sign</td><td>PosNeg</td></tr><tr style='background: white;'><td>Monday</td><td>$35.00</td><td>+</td><td>$35.00</td></tr><tr style='background: white;'><td>Tuesday</td><td>$49.00</td><td>+</td><td>$49.00</td></tr><tr style='background: white;'><td>Wednesday</td><td>$5.00</td><td>-</td><td>-$5.00</td></tr><tr style='background: white;'><td>Thurday</td><td>$65.00</td><td>+</td><td>$65.00</td></tr><tr style='background: white;'><td>Monday</td><td>$25.00</td><td>-</td><td>-$25.00</td></tr><tr style='background: white;'><td>Tuesday</td><td>$36.00</td><td>+</td><td>$36.00</td></tr><tr style='background: white;'><td>Wednesday</td><td>$20.00</td><td>-</td><td>-$20.00</td></tr><tr style='background: white;'><td>Wednesday</td><td>$69.00</td><td>+</td><td>$69.00</td></tr></table>


I have the following query:

SELECT Table1.Details, Table1.Amount, Table1.Sign, (([sign]="-")*[amount])+(([sign]="+")*-1*[amount]) AS PosNeg
FROM Table1;

It took me a while to work around the problem as :

true is -1 in Access 2007 and false is 0

so

if sign is negative it will return -1 so multiply by amount will give the negative amount
(([sign]="-")*[amount])

if sign is positive it will return -1 so multiply by -1, you will get 1 and multiply by amount you get the positive amount

(([sign]="+")*-1*[amount])

A bit far fetch for something that seems very simple

See if something like this adapted to your case may work!!!

When you GROUP and then SUM in the query the correct amount is returned

 

[Norie]

What is the CASE part of the SQL statement supposed to do?

Is it just returning the absolute value for ar_item.ar_amount or is it basing the value of ar_item.ar_amount on what's in ar_type.at_oper?

The reason I ask is because I don't actually think MS Query supports CASE but you could probably write a simple expression to do what you want.

As for the sum part you should just be able to set criteria for the result of the SUM.