Posted on 2002-06-27
Could anyone please help!
I want to connect 4 tables together to allow for access to information in other records.
I need to be able to look for the criteria of:
'If the 'PreInvoiceDate' field is less than or equal to a system date and for the 'InvoicedId' field to be blank.
The above works OK and retrieves a records information until I add the table named 'INVOICED' and the below information into the SQL query (the or statement):
'If the field InvoiceID is not blank (ie a record exists) then check for the 'FirstInvoice' field to be blank
If I add the above code then no information appears at all.
Here is the full code:
FROM CUSTOMER cu,
INVOICED i /*Problem Here*/
WHERE c.'contract number' = cu.'contract number' and
c.'contract number' = f.'contractid' and
c.'contract number' = i.'contractid' and /*Problem Here*/
f.'preinvoicedate' <= '08/28/2002' and
(f.'invoicedid' is null or (f.'invoicedid' is not null and i.'firstinvoice' is null)) /*Problem Here*/
Many Thanks in anticipation