Comparing two fields in a table

[Guest]

Hi!

I have two different "qty" fields in a table - one is from the purchase
order, the other is from the packing slip. These two qty's should match, but
if they don't, I need to know. Is there a way to write a query that would be
able to tell me that these two fields do not match? BUT, I do not want to
include any null records in my search.

Any help provided would be greatly appreciated!
Kat

 

[Guest]

Try the following SQL:

select poQty, psQty from TheTable
where not isnull (poQty <> psQty)

This will work if either or both fields are null, since the compare will
then be null<>null or null<>something, both of which evaluate to null and
therefore won't be selected.

I guess a question that needs to be asked is: When you say "BUT, I do not
want to
include any null records in my search.", what constitutes a NULL record?
Both of these fields being NULL? Or even if only one is null?

 

[Jeff Boyce]

Kat

I'm envisioning a query design grid with

* Qty_Purchased in one of the field "cells"
* <>Qty_Packed And Is Not Null in that column's Criterion "cell"
* Is Not Null in a second column's criterion "cell" -- this column has
Qty_Packed in the field "cell"

(in plain text, Packed isn't null, Purchased isn't null, and purchased isn't
equal to packed)

Good luck

Jeff Boyce
<Access MPV>

 

[Guest]

Try this

SELECT MyTable.*
FROM MyTable
WHERE (((MyTable.purchaseQty)<>[packingQty] And (MyTable.purchaseQty) Is Not
Null) AND ((MyTable.packingQty) Is Not Null))

 

[Guest]

Thank you Chaim for your help. After I asked the question I realized that I
stated it incorrectly. I should have said that a zero qty is okay, but blank
qty's are not.

Well this make a difference?

Kat

 

[Guest]

If they are numerical field there will not be any nulls.
In the design view of your query do this --
Field: PurchaseOrderField PackingSlipField
Criteria: <>[PackingSlipField]

 

[Guest]

Thanks a bunch Chaim and Jeff! I appreciate your help!

Kat