IIf statement in access

[KatSlif]

Hello

I need help with a formula in access please.

I have 12 codes that need to have either the letter A, B or C added to the end of the code depending on the age of a person:

e.g.
codes
AB01
AB02
AB20
AB21

If the persons age is 19 years and over I need an A at the end e.g. AB01A
If the persons age is between 6 and 18 years and over I need a B at the end e.g. AB01B
If the persons age is 5 and under I need a C at the end e.g. AB01C

I have the age calculated its just grouping to the correct code I need help with??

I have had ago:
CODE: IIf(

 In ("RD01","RD02","RD20","RD21","RN04","RN08","RN16","RN23","RN25","RN29","RN30","RN34"),IIf([Age]<6,[Code&"C"],IIf([Code] In ("RD01","RD02","RD20","RD21","RN04","RN08","RN16","RN23","RN25","RN29","RN30","RN34"),IIf([Age]<19,[Code&"B"],IIf([Code] In ("RD01","RD02","RD20","RD21","RN04","RN08","RN16","RN23","RN25","RN29","RN30","RN34"),IIf([Age]>=19,[Code&"A"]))))))

Any help appreciated
Katherine

 

[Rob65]

Hi Katherine,
it looks as though it should work, but why have you repeated "IIf(

 In ("RD01","RD02","RD20","RD21","RN04","RN08","RN16","RN23","RN25","RN29","RN30","RN34")" three times?  If it is true at the start of the statement, it is not going to become false half way through - you only need to test once.  Other than that I can't see why it wouldn't give the result you are looking for assuming that your ages are integers.
Kind regards,
Rob

 

[xenou]

[MyCode&"B"] is incorrect. You mean [MyCode]&"B"

CODE: IIf([MyCode] not in 
	("RD01","RD02","RD20","RD21","RN04","RN08","RN16","RN23","RN25","RN29","RN30","RN34"),
	[MyCode],
	IIf([Age]<6,[MyCode]&"C",IIf([Age]<19,[MyCode]&"B",[MyCodeCode]&"A")))

This will not work if Age is null so beware - make sure age is never null or else rewrite the formula to handle that case.
I have provided a values for when MyCode is not in the list of codes - which is to just leave it alone (return [MyCode]).

And I'm calling the field MyCode instead of Code because Code in brackets is a keyword for this website to create a code block.