<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=US-ASCII">
<TITLE>Query synatx</TITLE>
<META content="MSHTML 6.00.2600.0" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff size=2>How
about an if statement like the following</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff size=2>Select
OrderID,</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2> Price, </FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2> IIF(InvoiceType =
True, Quantity, Quantity * -1) as QTY</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff size=2>FROM
OrderHeader, OrderDetails</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff size=2>WHERE
OrderHeader.OrderID = OrderDetails.OrderID</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff size=2>You
get the jist?</FONT></SPAN></DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=326133116-24032003><FONT face=Arial color=#0000ff
size=2>Scott</FONT></SPAN></DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Pain, T. (Tim)
[mailto:Tim.Pain@e20.akzonobel.com]<BR><B>Sent:</B> Monday, March 24, 2003
11:22 AM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B>
[AccessD] Query synatx<BR><BR></FONT></DIV><!-- Converted from text/rtf format -->
<P><FONT face=Arial size=2>Dear List</FONT> </P>
<P><FONT face=Arial size=2>I have a OrderHeader and OrderDetails tables.
</FONT><BR><FONT face=Arial size=2>The OrderHeader contains a InvoiceType
field, set to True for Invoice and False for CreditNotes.</FONT> <BR><FONT
face=Arial size=2>The OrderDetails contains a Quantity field. This is always a
positive value, even for CreditNotes.</FONT> <BR><FONT face=Arial size=2>The
join between the tables is on OrderID.</FONT> </P>
<P><FONT face=Arial size=2>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.</FONT></P>
<P><FONT face=Arial size=2>This has got to be possible, but having spent most
of the day going nowhere with this, I need help.</FONT> </P>
<P><FONT face=Arial size=2>Any ideas would be much appreciated.</FONT> </P>
<P><FONT face=Arial size=2>Many thanks</FONT> <BR><B><I><FONT
face="Times New Roman" color=#000080>Tim Pain</FONT></I></B>
</P><BR><BR></BLOCKQUOTE></BODY></HTML>