IFF dosn't work

[amir0914]

Hi all,
I use calculated field in table with formula, but result is not true :

=IIf([amount]<"1000",[price],[amount]*[price])

when I type value less than "1000" in [amount] it gives [amount]*[price], while if [amount] be less than "1000" The field must gives [price]

for every number in amount it only gives [amount]*[price].

Can someone tell me problem of code?

 

[isladogs]

Amount has to be a number field for you to do this.
Remove the quotes around 1000

 

[amir0914]

Hi, Thank you for reply
Both Amount and Price are number field, but it works to "900" or any other numbers, only doesn't work for "1000" , and when I remove quotes around "1000" it gives error :

IIf([amount]<1000,[price],[amount]*[price])

 

[MajP]

do all records have an amount and price or are some null? If so wrap the fields in NZ.

 

[plog]

I use calculated field in table with formula...

Don't do this. While calculated values can be in a table, they shouldn't be. Instead, you should do the calculation in a query and then reference the query when you want this value.

As to your issue, can you take a screen shot of the table in design view? Not just the error message, but let us see the field types of amount and price and the expression you have for the calculated value.

 

[amir0914]

Hi all, thanks for reply
This is my table that I want to calculate price :

View attachment Price.accdb

 

[jdraw]

As was suggested use a query

SELECT tbl.amount, tbl.price, [amount]*[Price] AS LineTotal
FROM tbl;

amount	price	LineTotal
20	    200	4000
30	    400	12000

 

[amir0914]

Hi, if you check my uploaded access file, the field is number and when i remove quotes around "1000" then ok (as same below) but give error :

=IIf([amount]<1000,[price],[amount]*[price])

 

[jdraw]

I downloaded your database.
And retyped the expression in the calculated field Total.
Then, added a new record to tbl---- # 16.
And no error and seemed to work fine.

I have attached a png.

 

[amir0914]

Thank you, But you wrote the wrong code, : IIf([amount < 1000 ,.....)

can you attach the file here?

 

[jdraw]

??? But you wrote the wrong code
From your post #9

=IIf([amount]<1000,[price],[amount]*[price])

What is to attach? I showed the expression and the new record in the graphic.

As has been stated before, calculations should be done in queries or code behind forms.
Calculated field(s) in tables are avoided by most developers.

 

[amir0914]

Thank you all, my problem is resolved,
sorry for my poor english.

 

[isladogs]

amir
I'm glad you now agree that it works without the quotes ....as I first suggested back in post 2
Can you tell us why you kept saying it didn't work if you removed them.