IFF dosn't work (1 Viewer)

amir0914

Registered User.
Local time
Today, 09:18
Joined
May 21, 2018
Messages
151
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

MVP / VIP
Local time
Today, 16:18
Joined
Jan 14, 2017
Messages
18,186
Amount has to be a number field for you to do this.
Remove the quotes around 1000
 

amir0914

Registered User.
Local time
Today, 09:18
Joined
May 21, 2018
Messages
151
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])

Screenshot (939).png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:18
Joined
May 21, 2018
Messages
8,463
do all records have an amount and price or are some null? If so wrap the fields in NZ.
 

plog

Banishment Pending
Local time
Today, 11:18
Joined
May 11, 2011
Messages
11,611
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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:18
Joined
Jan 23, 2006
Messages
15,364
As was suggested use a query
Code:
SELECT tbl.amount, tbl.price, [amount]*[Price] AS LineTotal
FROM tbl;

Code:
amount	price	LineTotal
20	    200	4000
30	    400	12000
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:18
Joined
Feb 19, 2002
Messages
42,970
Did you do what Colin suggested and remove the quotes around the numeric value?

Strings are delimited with single or double quotes
Dates are delimited with pound signs (#)
Numbers use NO delimiter. When you enclose a numeric value in quotes, you tell Access that it is a string and so Access treats it like a string. That makes "1000" less than "900".
Why? because strings are evaluated character by character. Left to righe and 1 is less than 9. It makes NO difference how many characters follow. Numeric values are compared by first aligning by the implied decimal point so essentally 900 becomes 0900 (0 = leading zero to make the two values equal in size). And 0 is less than 1 so 0900 is less than 1000. This explanation is an oversimplification to make it easy for you to "see" the difference in comparing numbers vs strings. Essentially, strings are left justified but numbers are right justified and zero filled.
 
Last edited:

amir0914

Registered User.
Local time
Today, 09:18
Joined
May 21, 2018
Messages
151
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])

Screenshot (953).png
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:18
Joined
Jan 23, 2006
Messages
15,364
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.
 

Attachments

  • PriceAmountIssue.jpg
    PriceAmountIssue.jpg
    92.8 KB · Views: 84

amir0914

Registered User.
Local time
Today, 09:18
Joined
May 21, 2018
Messages
151
Thank you, But you wrote the wrong code, : IIf([amount < 1000 ,.....)

can you attach the file here?
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 12:18
Joined
Jan 23, 2006
Messages
15,364
??? 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

Registered User.
Local time
Today, 09:18
Joined
May 21, 2018
Messages
151
Thank you all, my problem is resolved,
sorry for my poor english.
 

isladogs

MVP / VIP
Local time
Today, 16:18
Joined
Jan 14, 2017
Messages
18,186
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.
 

Users who are viewing this thread

Top Bottom