New Record #Error

[chefdaveross]

I have a textbox in a subform that calculates this:
=NZ([ItemAmount]*[txtRecItemPrice])

it gives me the correct value, but then it displays #Error on the new record that has no data.

How can i get rid if the #error?

 

[mithani]

try this code

iif(isnull(NZ([ItemAmount]*[txtRecItemPrice])), 0, NZ([ItemAmount]*[txtRecItemPrice]))

 

[chefdaveross]

Still an error message

is it because i have a format in my control source in one of the textboxes?

[txtRecItemPrice] control source:
=Format([RecipeItem].[column](3),"Currency")

 

[mithani]

used = before code

=iif(isnull(NZ([ItemAmount]*[txtRecItemPrice])), 0, NZ([ItemAmount]*[txtRecItemPrice]))

 

[chefdaveross]

thats what I used:

=iif(isnull(NZ([ItemAmount]*[txtRecItemPrice])), 0, NZ([ItemAmount]*[txtRecItemPrice]))

any other thoughts?

 

[mithani]

Can you upload your db or is too large. what is the error message?

 

[chefdaveross]

I posted the pic above. There u can see where the #error is

 

[mithani]

can't see any picture, showing X. Why don't you attahed.

 

[CameronM]

You nay need to check for Null on both fields seperately. iif(NZ([ItemAmount]),0)*iif(NZ([txtRecItemPrice]),0)

 

[dkinley]

Instead of using the code as the default value or however it is employed. Try using it on the AfterUpdate() event of each control that populates its value (if you are not using it on these controls how else are you getting the most current value?)

Me.txtControlNameWhereDataGoes = Nz(Me.txtControlNameForItemAmount),0) * _
     Nz(Me.txtControlNameFortxtRecItemPrice),0)

In this manner there is no error because there is nothing done yet for the control.

-dK

 

[chefdaveross]

=iif(isnull(NZ([ItemAmount]*[txtRecItemPrice])), 0, NZ([ItemAmount]*[txtRecItemPrice]))

iif(NZ([ItemAmount]),0)*iif(NZ([txtRecItemPrice]),0)

Private Sub Text16_AfterUpdate()
Me.txtRecItemTotal = Nz(Me.ItemAmount),0) * _
Nz(Me.txtRecItemPrice),0)

All not working,
couldnt upload pictures so i posted error pics on my blog

http://chefdaveross.blogspot.com/2009/02/subform-errors.html

 

[boblarson]

Have you tried this:

=IIf(IsError(NZ([ItemAmount],0)*NZ([txtRecItemPrice],0)),0,NZ([ItemAmount],0)*NZ([txtRecItemPrice],0)))

 

[chefdaveross]

thanks,
says the expression i entered has too many closing parenthesis

If i take away the last parenthesis and use
=IIf(IsError(NZ([ItemAmount],0)*NZ([txtRecItemPrice],0)),0,NZ([ItemAmount],0)*NZ([txtRecItemPrice],0))

it works but am back to the same #error as the 1st example

http://tinyurl.com/dcogkv

 

[boblarson]

thanks,
says the expression i entered has too many closing parenthesis

Try this one:

=IIf(IsError(NZ([ItemAmount],0)*NZ([txtRecItemPrice],0)),0,NZ([ItemAmount],0)*NZ([txtRecItemPrice],0))

 

[chefdaveross]

yes, tried, see above

back to original #error

http://tinyurl.com/dcogkv

 

[boblarson]

Time to post the db (after 15 posts in a thread and it isn't resolved, that is a quicker way to resolve it).

 

[chefdaveross]

Ok thanks

see the subform in database

 

[mithani]

try this one.

=iif(isnull(recipelteml),0,NZ([ItemAmount]*[txtRecItemPrice]))

recipelteml is your first column (please correct spelling)

 

[mithani]

see attached.

I haven't touched text14 col.

 

[chefdaveross]

Great thanks,
that did it.

I was using txtbox 14 to total the recipe.
in the subform footer

=Sum([txtRecItemTotal])

then call it up on the main form

now having trouble getting that to work.
thats the final piece to the puzzle on this form

thanks