[Access] calculated field with condition
Hello,
I have a syntax issue with a field in a report.
It's a calculated field that calculates a change between figure A and figure B.
So in the properties, the Control Source of the field = (B-A)/A
It works, but I have a problem if A = 0; it shows #Div/0 (which is normal...). So I put
=IIf(B=0,0,((B-A)/A))
and there I get an error "The number in the expression is not valid".
I think my syntax is correct though. Does anyone have an idea?
For your information, the field has a percentage format.
Thanks in advance to whoever can help me out; I've been stuck for a while trying all sorts of syntax.
I have a syntax issue with a field in a report.
It's a calculated field that calculates a change between figure A and figure B.
So in the properties, the Control Source of the field = (B-A)/A
It works, but I have a problem if A = 0; it shows #Div/0 (which is normal...). So I put
=IIf(B=0,0,((B-A)/A))
and there I get an error "The number in the expression is not valid".
I think my syntax is correct though. Does anyone have an idea?
For your information, the field has a percentage format.
Thanks in advance to whoever can help me out; I've been stuck for a while trying all sorts of syntax.
3 answers
-
Well, actually, I wanted to calculate my field in the query. I had the same error, but in the query, the assistant corrects the mistakes, so the correct syntax is actually:
=TrueFalse([SALES MONTH YEAR (N-1)]=0;0;([SALES MONTH YEAR N]-[SALES MONTH YEAR (N-1)])/[SALES MONTH YEAR (N-1)])
By copying this formula into the calculated field directly in the report, it works.
Thank you anyway for making me realize that I could calculate the percentage in the query. But as a novice in Access, I don’t have the reflex to use queries all the time.
