Selecting records that may or may not contain null fields

[NBVC]

I have 3 tables: CUSTOMER_ORDER, SHIPPER, SHIPPER_LINE

The SHIPPER table has a field called INVOICE_ID.

I want to select records from the SHIPPER where INVOICE_ID may or may not be Null, at the same time I am filtering for a field in SHIPPER_LINE called SERVICE_CHARGE_ID (parametered)

So user enters a Service charge Id and all the Orders come up whether there is an invoice or not.

Thanks for any advice

 

[Turkbear]

Hi,
What field(s) link the tables?

Especially how is CUSTOMER_ORDER linked to SHIPPER?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[NBVC]

Hi,

ID field in CUSTOMER_ORDER is linked to CUSTOMER_ORDER_ID in the SHIPPER table which is also linked to CUSTOMER_ORDER_ID in the SHIPPER_LINE table. Then I've got PACKLIST__ID in the SHIPPER table linked to the PACKLIST_ID in the SHIPPER_LINE table.

In case you are wondering... the Database comes from our ERP system, which is Visual Manufacturing...

 

[Madawc]

If INVOICE_ID may be null, then any test must begin with soemthing like

if not isnull(INVOICE_ID)...

Any Crystal formula will stop if it hits a null, even if nulls are allowed for later on.

I also don't quite see what your problem is, unless you are doing some sort of selection involving INVOICE_ID. What have you tried and what happens?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[NBVC]

I think I figured out the problem... some of the items that were not "invoiced" still hadn't been "shipped" so they were not in the SHIPPER and SHIPPER_LINE tables yet... Once I shipped them, they appeared on my report even though they were not yet invoiced...

Thanks.