In a Join, 0 doesn't join -0; why not? how to workaround?

[Dick Watson]

I have a table of transaction data. In the imported state of the data, there
are may cases where the same transaction is in there in two rows from both
accounts that are transfer to/transfer from halves of the same transaction,
roughly:

row 1
TxnKey=50, Amt=100, Date=1/31/2005, AcctPtr=123 ...
row 2
TxnKey=51, Amt=-100, Date=1/31/2005, AcctPtr=321 ...

I have a pair of queries that select the transactions that have
corresponding halves in the table so that I can then programmatically do all
of the linkages and then delete the extra row. In order to join these
transactions by date, account, and amount, the queries produce rows like:

qselKeeperTxns:
KeeperTxnKey=50, KeeperActPtr=123, KeeperDate=1/31/2005, Amt=100

qselLinkAndDeleteTxns:
Select ... -[Amt] As NegAmt ...
DeleteTxnKey=51, UnlinkAcctPtr = 123, LinkActPtr=321,
DeleteDate=1/31/2005, NegAmt:=100

I then join them on
(qselKeeperTxns.KeeperActPtr=qselLinkAndDeleteTxns.UnlinkAcctPtr) AND
(qselKeeperTxns.KeeperDate=qselLinkAndDeleteTxns.DeleteDate) AND
(qselKeeperTxns.Amt=qselLinkAndDeleteTxns.NegAmt)

This works exactly as desired for all cases but the case where Amt=0 in both
of the original rows. For these, the query does not join the row.

I've tried not negating the Amt in the qselLinkAndDeleteTxns query and then
joining on
(qselKeeperTxns.Amt=-qselLinkAndDeleteTxns.Amt)

No Joy.

If I query like:

Select qselKeeperTxns.Amt, qselLinkAndDeleteTxns.NegAmt ...
Where (qselKeeperTxns.Amt=0 And qselLinkAndDeleteTxns.NegAmt=0)...

The row show up fine, as expected.

If I query like:

Select (qselKeeperTxns.Amt=qselLinkAndDeleteTxns.NegAmt) As TestEqual
....

I get True, as expected.

If I try not negating the Amt in the qselLinkAndDeleteTxns query and then
joining on:
(qselKeeperTxns.KeeperActPtr=qselLinkAndDeleteTxns.UnlinkAcctPtr) AND
(qselKeeperTxns.KeeperDate=qselLinkAndDeleteTxns.DeleteDate) AND
(qselKeeperTxns.Amt=qselLinkAndDeleteTxns.Amt)

I get the 0 amount row, but obviously I don't get anything else I wanted.

The underlying type of Amt is Decimal. So, I tried the qselLinkAndDeleteTxns
query like:

...CDec(-[Amt]) As NegAmt...

for qselLinkAndDeleteTxns, but it refuses the CDec() saying I've entered a
function with the wrong number of arguments. Maybe this has something to do
with CDec returning a Variant??? I sure don't know of more than one argument
to CDec().

 

[Brendan Reynolds]

Perhaps ...

Select ... IIf([Amt] = 0, 0, -[Amt]) As NegAmt ...

 

[Dick Watson]

Great idea! And it works! (Actually I used:

Select ... IIf([Amt] = 0, [Amt], -[Amt]) As NegAmt ...

)

It still seems odd that I should have to do this...