[Access] calculated field with condition
euskal75
Posted messages
18
Status
Member
-
blux Posted messages 5021 Registration date Status Moderator Last intervention -
blux Posted messages 5021 Registration date Status Moderator Last intervention -
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
Actually, I'm wondering if the names of my fields aren't causing a bit of panic, my formula is exactly:
=IIf([VENTES MOIS ANNEE (N-1)]=0,0,(([VENTES MOIS ANNEE N]-[VENTES MOIS ANNEE (N-1)])/[VENTES MOIS ANNEE (N-1)]))
But this is not a query. I'm in a report and it's a calculated field based on fields that are indeed the result of a query.
My query calculated [VENTES MOIS ANNEE (N-1)] and [VENTES MOIS ANNEE N] and I'm calculating the evolution percentage directly in the report
I have a feeling you're going to tell me to calculate the percentage in the query....
=IIf([VENTES MOIS ANNEE (N-1)]=0,0,(([VENTES MOIS ANNEE N]-[VENTES MOIS ANNEE (N-1)])/[VENTES MOIS ANNEE (N-1)]))
But this is not a query. I'm in a report and it's a calculated field based on fields that are indeed the result of a query.
My query calculated [VENTES MOIS ANNEE (N-1)] and [VENTES MOIS ANNEE N] and I'm calculating the evolution percentage directly in the report
I have a feeling you're going to tell me to calculate the percentage in the query....
