Err

[Rhubie]

Field in query - no error

on form field (which is not enterable, just displayed - if it is neither Transfer Rolls or N - it gives #Err instead of null value

TransferRollPrice: (Int(IIf([Classification]="Transfer Rolls",[USDollar]+[RetailMargin],IIf([Classification] ="Transfer Rolls" AND Mid([WBCode],4,1="N"),([USDollar]+[WBShipping])+[RetailMargin],Null))/0.05)*0.05)+0.0

There should be no value in the form field if it is neither - not sure what I am doing wrong?

[June7]

You are putting this calc in textbox ControlSource? If it works in query, just do it in query and bind textbox to the field name.

Misplaced paren:
(Int(IIf([Classification]="Transfer Rolls",[USDollar]+[RetailMargin],IIf([Classification]="Transfer Rolls" AND Mid([WBCode],4,1)="N",([USDollar]+[WBShipping])+[RetailMargin],Null))/0.05)*0.05)+0.0

However, if Classification = "Transfer Rolls, the "N" condition will never take effect. Try:

Int(IIf([Classification]="Transfer Rolls",([USDollar]+[RetailMargin]+IIf(Mid([WBCode],4,1)="N",[WBShipping],0))/0.05,Null)*0.05

[Rhubie]

I have it in a query - I chose the field from the query? Not sure why I get this - do I need to rebind the field if I changed the query?

[June7]

If the constructed field name is not changed, then the binding is not changed.

I was editing my previous post while you replied. Review again.

[Rhubie]

I am only getting the #ERROR when I leave it blank -

[June7]

When you leave what blank - Classification?

My suggested expression should handle that.

If you want to provide db for analysis, follow instructions at bottom of my post. Identify the objects involved in this issue.