#Size! and #Type! textbox errors

Question

Hello,

I have  textboxes with calculations and I am getting both of these errors. I can't seem to figure out why.

this is the data inside my textboxes

txttotalpayment = [SubFrmContractPayment].[Form]![txtPaymentTotal] This textbox displays correctly

txtcontractbal=[contractamount]-[txttotalpayment]-[txtretention] This textbox displays #Type!

txtcontractbal = Nz([contractamount])-Nz([txttotalpayment])-Nz([txtretention]) This textbox displays #Size! when I add NZ

txtretention =Nz([txttotalpayment])/(1-Nz([contractretention]))-Nz([txtcontractbal]) This textbox displays #Size!

Your help would be greatly appreciated!

Thank you

Answer 1

As I said you are referencing text box names rather than fields. 

You need to repeat the calculation like this --

Instead of using -- txtcontractbal=[contractamount]-[txttotalpayment]-[txtretention]

  use  -- txtcontractbal=[contractamount] - ([SubFrmContractPayment].[Form]![txtPaymentTotal]) - [txtretention]

Do the same for the others.

Answer 2

this is the data inside my textboxes

txttotalpayment = [SubFrmContractPayment].[Form]![txtPaymentTotal]

txtcontractbal=[contractamount]-[txttotalpayment]-[txtretention]

txtcontractbal = Nz([contractamount])-Nz([txttotalpayment])-Nz([txtretention])

txtretention =Nz([txttotalpayment])/(1-Nz([contractretention]))-Nz([txtcontractbal]) 

Is the BOLD the names of the text boxes?  If so then you are referencing text box names rather than fields -- that want work without doing a complete reference to include the name of the form where the text box is located.

Answer 3

You should be able to combine more than two references to other controls into the control source of another control. I would make sure the names are correct and you handle nulls correctly.

Answer 4

I tried it with no success. I am assuming the textbox doesn't accept more than 2 other fields in the calculation. I tried adding only two fields at a time and it worked. The only thing is that I have to create more textboxes to come up with the expected result.

for example:

txtcontractbal1=Val([contractamount])-Val([txttotalpayment])

txtcontractbal2 = Val([txtcontractbal1])-Val([txtretention])

That is the only way it worked, but if you know of a simpler way to do it please let me know!

Thank you :)

Answer 5

I never use Nz() without specifying the default value.

  Nz([contractamount],0)

Is it possible you are dividing by zero?

Try:

txtcontractbal=Val([contractamount])-Val([txttotalpayment])-Val([txtretention])