[AccessD] Query synatx

How about an if statement like the following
 
Select OrderID,
          Price, 
          IIF(InvoiceType = True, Quantity, Quantity * -1) as QTY
FROM OrderHeader, OrderDetails
WHERE OrderHeader.OrderID = OrderDetails.OrderID
 
You get the jist?
 
Scott

-----Original Message-----
From: Pain, T. (Tim) [mailto:Tim.Pain at e20.akzonobel.com]
Sent: Monday, March 24, 2003 11:22 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Query synatx



Dear List 

I have a OrderHeader and OrderDetails tables. 
The OrderHeader contains a InvoiceType field, set to True for Invoice and False
for CreditNotes. 
The OrderDetails contains a Quantity field. This is always a positive value,
even for CreditNotes. 
The join between the tables is on OrderID. 

What I am trying to do is create a query that will return a single value for all
the quantities on order in the OrderDetails, for a given product number. So
where the InvoiceType = False, the Quantity will have to be Quantity * -1.

This has got to be possible, but having spent most of the day going nowhere with
this, I need help. 

Any ideas would be much appreciated. 

Many thanks 
Tim Pain 



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030324/bfa0420d/attachment-0001.html>