Cannot format and expression

[Skip Bisconer]

I have a query built from a linked Excel sheet. In the query I created a new field with an IIF statement subtracting one field from another, and if the result was lower than one of the fields it was to show the total, and if not, remain blank. Below is the expression:

UnderOverLP: IIf([QtyOnHand]-[LP]<[LP],[QtyOnHand]-[LP],"")

My problem is this number arrives with excessive places after the decimal. So I tried to set the numeric properties for this field and the property option is blank. Is this normal or am I doing something incorrectly? I have tried Round but then I get #Error in the rows that should be blank.

Any help would be appreciated.

 

[neileg]

You're getting an error on the blank lines because you have used "" which is a text value, not numeric. Set these lines to zero instead and it will work. It's never a good idea to mix different datatypes in a field.

 

[Skip Bisconer]

Thanks Niel,

I knew it had to be something simple.