Does the problem occur if you use 0 (zero) instead of EMPTY in the IIF?
----- Original Message ----
From: John Weller <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Sent: Tuesday, January 22, 2008 7:22:00 PM
Subject: SQL Problem
Hi Guys,
I have a problem that is driving me nuts!
I have 2 tables holding details of stock movements, referred to as
Transactions, TransHeader and TransDetails. TransHeader holds details
of
the transaction type, TransType, which can be Goods In (GI), Transfer
(TF)
or Goods Out (GO). There are 2 tables, Suppliers and Locations each
with an
integer ID field and a name. TransHeader has 4 integer fields to hold
FromSuppID, FromLocnID, ToLocnID and ToSuppID. If the TransType is GI
the
FromSuppID and ToLocnID are populated with values and the others are
zeroes,
if it is TF the SuppID fields are zero with values in the others, etc.
I
need to populate a cursor with details of TransDate, TransType,
FromSuppName, FromLocnName, ToLocnName, ToSuppName for each record in
TransDetails where the stockID has a particular value. I have used the
View
generator to create an SQL statement which I have modified by adding
conditionals to test for zeroes as shown below:
SELECT Transheader.transdate, Transheader.transtype,;
IIF(EMPTY(Transheader.fromsuppid), SPACE(50), Suppliers.suppname) as
FromSuppName, ;
IIF(EMPTY(Transheader.fromlocnid), SPACE(50), Locations.locnname) as
FromLocnName, ;
IIF(EMPTY(Transheader.tolocnid), SPACE(50), Locations_a.locnname) as
ToLocnName, ;
IIF(EMPTY(Transheader.tosuppid), SPACE(50), Suppliers_a.suppname) as
ToSuppName, ;
Transdetails.qty;
FROM ;
kactstock!transheader ;
INNER JOIN kactstock!transdetails ;
ON Transheader.transid = Transdetails.transid ;
INNER JOIN kactstock!suppliers Suppliers_a ;
ON Transheader.tosuppid = Suppliers_a.suppid ;
INNER JOIN kactstock!locations Locations_a ;
ON Transheader.tolocnid = Locations_a.locnid ;
INNER JOIN kactstock!locations ;
ON Transheader.fromlocnid = Locations.locnid ;
INNER JOIN kactstock!suppliers ;
ON Transheader.fromsuppid = Suppliers.suppid ;
ORDER BY TransHeader.Transdate descending ;
WHERE transdetails.stockid = 578
It works perfectly if all of the ID fields are positive but if any are
zero
there is no result despite the IIF statements.
I'm sure it is something very simple that I cannot see but I'm baffled.
Help!!!
TIA
John Weller
01380 723235
07976 393631
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
