Create a Calculation to Produce Text

[DigitalAdrenaline]

Hi Guys, just trying to create a calculation in a table field called BCAD which isn't working. I'm trying to calculate:

iif(period < 0, "BC", iif( period >0, "AD", ""). Just wondering if someone can point out my error. Screenshot below. thanks.

[June7]

What is the error message?

You are missing a closing paren in that expression. Parens and brackets as well as quote marks and apostrophes must always be in pairs.

But why use the second IIf()? Will 0 ever be a value in the field?

IIf([period] < 0, "BC", "AD")

[DigitalAdrenaline]

Hi June,

Problem solved. I was attempting to build the expression in my main table in Design View. In fact, I can only build expressions in either Forms, Queries and Reports. So I opened the control source of BCAD in Properties and built the expression as follows:

iif([txtPeriod] <0,"BC",iif({txt.period] >0,"AD",""))

Works perfectly. Cheers.

[CJ_London]

you could also just use the format property

"AD";"BC";"":""

[isladogs]

you could also just use the format property

"AD";"BC";"":""

Just to expand upon Ajax's suggestion, the 4 format conditions correspond to positive; negative; zero; null values

[CJ_London]

just a thought - there wasn't a year 0 so formula just needs to be

iif([txtPeriod] <0,"BC","AD")

or formatting

"AD";"BC";"AD"

[June7]

Certainly can do calcs in table with Calculated type field. Apparently, BCAD already had data and therefore could not convert to calculated field. Could have deleted and recreated the field as calculated type. Most experienced developers would recommend not using calculated field in table, however, your expression should work. It does for me.