simple update query question with a nested if statement 2

[elektra12]

i'm new at sql statements in access, particularly nested ones. One trying to run an append query based upon a checkbox value. Here's what I want to accomplish. If the box is NOT checked, then I want to enter the value for CustID (from TblTransactions) into a certain field "Owner" (belonging to TblAddress). If the box IS checked, I want to erase the contents of field "Owner", but ONLY if the value of Owner is NOT equal to the value of CustID for the latest transaction (in which the box is or isn't checked). Mostly the query works, but the nested part of it does not work. For instance, if the box is CHECKED, and the corresponding "Owner" field IS EQUAL to CustID for the latest transaction, then it erases the value in the Owner field anyway, though it shouldn't. Maybe if I attach my code this will make more sense. Thanks.

UPDATE tblAddress, tblTransactions
SET tblAddress.Owner =
IIf
(([TblTransactions].[Seller]=1),
IIf
(([tblAddress].[Owner])=([tblTransactions].[CustID]),Null),
([Forms]![CustTrans]![CustomerID]))

WHERE
((([tblTransactions].[TransactionID])=
DMax("[TransactionID]","TblTransactions&quot)
AND(([tblAddress].[ID])=[TblTransactions].[AddressID]));

 

[scriverb]

Let's establish something first. The checkbox values. Checked = 1 and Not Checked = 0. Or is this value a Boolean and we should be looking at True(-1) and False(0)? Bob Scriver

 

[elektra12]

yikes, somehow in my post, the minus (-) got deleted in my code. We're dealing with True/False. Sorry for the typo...

 

[scriverb]

elektra12: Try this:

UPDATE tblAddress, tblTransactions
SET tblAddress.Owner =
IIf
( ([TblTransactions].[Seller]= -1),
IIf
(([tblAddress].[Owner])=([tblTransactions].[CustID]), Null,
([Forms]![CustTrans]![CustomerID]), Null )

WHERE
((([tblTransactions].[TransactionID])=
DMax("[TransactionID]","TblTransactions&quot)
AND(([tblAddress].[ID])=[TblTransactions].[AddressID]));


I think you were missing one of your parameters in the nested IIF. Since I can't test it out you will have to do that. Let me know if this solves your problem. Bob Scriver

 

[elektra12]

Thank you for your post. I tried it, but it said I had the wrong number of arguments. This was after I deleted one extra parenthesis you had which brought up an error msg.As for the nested query, and your suggestion, I think that if I need a second result to the if statement (the optional one which results if the tested condition isn't true), then that second result should be in the nested portion. The ([Forms]![CustTrans]![CustomerID]) option is supposed to function as the second result of the OVERALL if statement. But I probably should have the equivalent of this in the nested portion (after the null), but I don't know what to put there. I need something that would tell the db to keep the original info in tact instead of erasing it. In an ideal world, there would be something like that, or maybe in VB, which I barely know at all. Is that clear as mud? I figured out another way to do kindof what I'd like to, though not as good as I would hope. Actually, its probably not much better than the other way, but i'm to tired to know the difference. I handled it in the WHERE statement instead. Here's what I did:

UPDATE tblAddress, tblTransactions
SET tblAddress.Owner =
IIf
(([TblTransactions].[Seller]=-1),Null,([Forms]![CustTrans]![CustomerID]))

WHERE (((tblAddress.Owner)=([TblTransactions].[CustID]) Or (tblAddress.Owner) Is Null) AND ((tblTransactions.TransactionID)=DMax("[TransactionID]","TblTransactions&quot) AND ((tblAddress.ID)=([TblTransactions].[AddressID])));

The one flaw with this would be in the rare case that one of our customers BUYS the same home that was SOLD by another one of our customers. If the "sell" transaction is done first (checked box) then all is fine and then the old owner's ID is erased from that address, leaving a null field for the new buyer's ID to occupy this same address, when that form is filled out. But if the BUYER'S transaction is done first, the buyer won't show up as the new owner of the address (instead, the old owner will remain) and then when the "sale" transaction is done, the old owner's name WILL be erased finally, but leaving the Owner field empty. Still, this would rarely happen, so I'll just have to manually enter if/when it does happen. I also tried running a separate query in the macro that deals with the condition of the box NOT being checked, but that basically caused the same problem, so I took it out. It seems like there's something terribly simple that I'm missing, but I'm too new at this to know what. Sorry for the long post...

 

[scriverb]

Don't get discouraged. We can get this to work. Try this:

UPDATE tblAddress, tblTransactions
SET tblAddress.Owner =
IIf
([TblTransactions].[Seller]= -1,
IIf
([tblAddress].[Owner<>[tblTransactions].[CustID], Null, [tblAddress].[Owner],
), [tblTransactions].[CustID] )
WHERE
((([tblTransactions].[TransactionID])=
DMax(&quot;[TransactionID]&quot;,&quot;TblTransactions&quot)
AND(([tblAddress].[ID])=[TblTransactions].[AddressID]));


I think this will cover all of the situations as you described them and will not require manual intervention. Bob Scriver

 

[elektra12]

IT WORKS! Thanks for the helpful suggestion. The only change I had to make was to change <> to = for the purposes of what I was trying to accomplish, but it does everything that I need it to do, even in the rare case that I described before. I knew it had to be simple! Thanks again for your time and patience!

 

[scriverb]

Glad to be of help. Bob Scriver