Update a field from another fields with zero values

[achillese]

I need help on this.

I have a table of which REMITAMTINCLADJ is updated from REMITAMTINCGST, ADJ1, ADJ2, and ADJ3. All fields reside in the same table and are of currency type.

I am using Update Query to update the RemitAmtInclAdj

The formula in the Update Query is:

[RemitAmtincGST]+[Adj1]+[Adj2]+[Adj3]

The RemitAmtIncGST always have an amount in it but Adj1, Adj2, Adj3 could be empty fields.

When I run the query it will not update the RemitAmtInclAdj.
However, if I put any values in Adj1, Adj2,Adj3, the RemitAmtInclAdj is updated when I run the query.

Shown below is the actual query
===============================

UPDATE Connects_Master SET Connects_Master.Remitted = Yes, Connects_Master.RemitAmtInclAdj = [RemitAmtincGST]+[Adj1]+[Adj2]+[Adj3]
WHERE (((Connects_Master.UNITCOST)>0) AND ((Connects_Master.BatchNmbr) Is Not Null));



Thank you in advance.

 

[PHV]

Perhaps this ?
UPDATE Connects_Master
SET Remitted = Yes, RemitAmtInclAdj = [RemitAmtincGST]+Nz([Adj1],0)+Nz([Adj2],0)+Nz([Adj3],0)
WHERE UNITCOST>0 AND BatchNmbr Is Not Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[achillese]

It worked, brilliant! Thank you for that...