Multi iif condition in report

[kitty12]

Hi,

I work with a report in Access 2000 that I should change it. I need to use three values,361, 362 and 363.
As an example, if Tot_num = 133, in this field should be shown 3613. I wrote this :

IIf(Left([Tot_num];2)="12";"";"363" & Right([Tot_num];1)),IIf(Left([Tot_num];2)="11";"";"362" & Right([Tot_num];1)),IIf(Left([Tot_num];2)="13";"";"361" & Right([Tot_num];1))

But when I open the report it shows me a message that it has a syntax error.
Could you help me with that? How can I use multi IIF in reports?

Thanks in advance.

 

[SydneyGeek]

If Tot_Num is a number you can't treat it like text. You would need to do some maths, eg assuming that the values are between 100 and 1000:

Iif(Int([Tot_Num]/10)=13,3610+[Tot_Num] Mod 10,... etc)

Denis

 

[kitty12]

If Tot_Num is a number you can't treat it like text. You would need to do some maths, eg assuming that the values are between 100 and 1000:

Iif(Int([Tot_Num]/10)=13,3610+[Tot_Num] Mod 10,... etc)

Denis

Thanks Denis for your reply. I'm new in Access.
I didn't understand this part of your condition : "... ,... etc)". I wrote it :

IIf(Int([Tot_Num]/10)=11,3620+[Tot_Num] Mod 10, IIf(Int([Tot_Num]/10)=12,3630+[Tot_Num] Mod 10, IIf(Int([Tot_Num]/10)=13,3610+[Tot_Num] Mod 10)))

but I still have error message for it. Could you guid me how to write it for others 362 and 361?

for example:

if Tot_Num = 112 show 3622 (362 and 2 (first digit of 112 from right))
if Tot_Num = 122 show 3632 (363 and 2 (first digit of 122 from right))
if Tot_Num = 133 show 3613 (361 and 3 (first digit of 133 from right))

Regards,
Kitty

 

[SydneyGeek]

Hi Kitty,
Try...

IIf(Int([Tot_Num]/10)=11,3620+[Tot_Num] Mod 10, IIf(Int([Tot_Num]/10)=12,3630+[Tot_Num] Mod 10, IIf(Int([Tot_Num]/10)=13,3610+[Tot_Num] Mod 10,0)))

Just like the If function in Excel you need a final option to cover results where the other conditions are not met.

Denis

 

[james_lankford]

IIf(Left([Tot_num];2)="12";"";"363" & Right([Tot_num];1)),IIf(Left([Tot_num];2)="11";"";"362" & Right([Tot_num];1)),IIf(Left([Tot_num];2)="13";"";"361" & Right([Tot_num];1))

you're using semi colons
you should be using commas

 

[kitty12]

Thanks Denis. Now it works

 

[kitty12]

you're using semi colons
you should be using commas

Thanks james_lankford.