Selection formula please

[markajem]

I have a report where I want to pull only certain records from two tables.

TableBookCnt
TablePhyCnt

If the field Qty in both tables are not equal I want that record to print, but also if the record exists in TablePhyCnt but not in TableBookCnt I want it to print. This is for a physical inventory report. The table TablePhyCnt may have a record for that item whereas originally it did not in the TableBookCnt table. Both tables compensate for Bins>Product> and may have more than one pack master so therefore may have more than one entry.

My problem is that I am trying only to capture the Bins>Products where there is a variance in the PhyCnt to the BookCnt.

Any ideas on what my selection formula should look like. I already have if TableBookCnt.qty <> TablePhyCnt.qty then it will show the record but just can't get it to show the record if there is a value in TablePhyCnt but not in TableBookCnt.

[tt]
TablePhyCnt TableBookCnt

rowid ========>rowid (joined with leftouter)
binnum ========>binnum
product product
qty qty
[/tt]

The tables TablePhyCnt and TableBookCnt are the exact same files with the exact same fields the only difference is that the table TableBookCnt is the original inventory quantities file as a backup and the table TablePhyCnt is the active inventory quantities file. This way I can find my variances from the physical count to the original book count.

Thanks
Mark
Mark
Email: markanas333@hotmail.com

 

[dgillz]

Isnull({TablePhyCnt.Qty}) or
{TableBookCnt.Qty} <> {TablePhyCnt.Qty}

Always put the null test first on a statement like this.
Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[markajem]

Thanks Don, that worked great.

Mark
Mark
Email: markanas333@hotmail.com