IIf in Expression Builder

[safril]

Hi, i'm newbie in Access. I have a field that contains a list of month names in a year and I want to convert it automatically to roman numerals in another field. I've used the IIf function but it only returns 'XII' for all month names. Am I missed something? This is my Expression Builder syntax:

IIf([BPerm_Banding]="Januari";"I";IIf([BPerm_Banding]="Februari";"II";IIf([BPerm_Banding]="Maret";"III";IIf([BPerm_Banding]="April";"IV";IIf([BPerm_Banding]="Mei";"V";IIf([BPerm_Banding]="Juni";"VI";IIf([BPerm_Banding]="Juli";"VII";IIf([BPerm_Banding]="Agustus";"VIII";IIf([BPerm_Banding]="September";"IX";IIf([BPerm_Banding]="Oktober";"X";IIf([BPerm_Banding]="November";"XI";"XII")))))))))))

Please help. Thanks in advance

 

[moke123]

What is the datatype of [BPerm_Banding]? Your getting XII because none of the conditions in your expression are being met.
You can probably also use the Switch function.

I'm also not sure if using a comma as opposed to a semi-colon makes a difference. In english versions of access you'd use a comma but some other versions use a semi-colon.

 

[plog]

So wait, you have a list of values that you want to relate to another set of values? Like one set of values could be in a vertical list and right next to each value could be a vertical list of the values you want them to relate to?

Perhaps code in Access isn't the way to implement this solution.

 

[arnelgp]

you can actually build another table with those month and roman numbers:

tblRomanMonth
Month (short string)
Roman (short string)
MonthNum (integer)

Month	Roman	MonthNum
Januari	I	1​
Februari	II	2​
Maret	III	3​
April	IV	4​
Mei	V	5​
Juni	VI	6​
Juli	VII	7​
Agustus	VIII	8​
September	IX	9​
Oktober	X	10​
November	XI	11​
Desember	XII	12​

you can add this Table to your Query:

SELECT yourTable.*, tblRomanMonth.Roman
FROM yourTable
LEFT JOIN
tblRomanMonth ON yourTable.[BPerm_Banding]=tblRomanMonth.Month