Update Query - Negative Numbers

[Guest]

I have created an inventory database. I have a current inventory table with
a field called Transfer (Long Integer) used for both transfer in and transfer
out. I have another table called tblTransfer, where the user selects what
location he wants to transfer inventory out of and where to transfer into.
I am linking the two tables and using an update query to update the transfer
amount - it works fine if the transfer amount in my current inventory table
is zero. I use an append query to add the transfer out location and that is
fine. For the update query, if the transfer amount already holds a number (at
first I just thought a negative number, but I believe regardless), it will
say zero records updated. I'd rather not use code to get around this as I'm
not that savy with it - can anyone think of what the problem might be. One
other fly in the ointment - I can transfer out of the same location into
different locations, so in affect its hitting the same record twice.
Interestingly enough the first time through it works for both records, but
then when I try to do another transfer on that same location it doesn't work.
Any help/suggestions would sure be appreciated!! Thanks!!!

 

[Douglas J. Steele]

What does your Update query look like?

 

[Guest]

UPDATE tblCurrentInventory INNER JOIN tblTransferInventory ON
tblCurrentInventory.InventoryID=tblTransferInventory.TFInventoryID SET
tblCurrentInventory.Transferred =
tblCurrentInventory.Transferred-tblTransferInventory.TFQuantity
WHERE (((tblCurrentInventory.Transferred)=0));

 

[Douglas J. Steele]

I don't understand why you have

WHERE (((tblCurrentInventory.Transferred)=0));

in there.

 

[Guest]

After the Update Query I have all records that were updated set the
TransferFlag = -1 - meaning those records were already transferred. So upon
doing this update query, if there are records that have already been
transferred they won't be updated twice. The user has the option of deleting
the transfer records or keeping them out there to view. I don't think this
has anything to do with the problem I'm having. Its so frustrating because
it works the first time through but once there is a number other than zero as
transferred it doesn't work. Any ideas would be so appreciated!!!

 

[Guest]

Hi - after looking at the query again, I realized you were right about the
where clause - that was a big duh on my part - I was pointing to the wrong
table - it should have been tblTransferInventory.Transferred as that is the
field I set up to indicate whether the data was transferred - lesson for me,
never use the same field name in two tables that mean two different things!!
Thanks for making me take a closer look at it!