data type mismatch in criteria expression

[jennifuh]

I have an update query for tGLCashAccount where it adds a value from another table with the BeginningBalance to arrive at CurrentBalance.

Here's what it looks like in design view:

Field: CurrentBalance
Table: tGLCashAcct
Update to: [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]


Here is SQL code:
UPDATE tGLCashAcct, tMakeNewCashBal SET tGLCashAcct.CurrentBalance = [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
WHERE (((tGLCashAcct.GLCashAcctID)="102"));


I get the error: data type mismatch in criteria expression when I run it.

 

[boblarson]

Probably a null value. Encapsulate each field with the NZ function:

Update to: Nz([tMakeNewCashBal].[TotalPrice],0)+Nz([tGLCashAcct].[BeginningBalance],0)

 

[jennifuh]

Still getting same error All these fields are set to currency as well.

I tried making CurrentBalance a Calculated field instead and now it says that the field is not updatable.

 

[boblarson]

WHERE (((tGLCashAcct.GLCashAcctID)="102"));

is GLCashAcctID numeric or Text? If it is numeric get rid of the quotes on the number

WHERE (((tGLCashAcct.GLCashAcctID)=102));