I have an Access database that tracks an important qualification that has deadlines based on your rank. I have a query that will calculate the start date for the qualification based on the date they arrive to the command but I need the following equation to give me the date they will go delinquent if they do not obtain the qualification. There is a couple variables first if this is a requalification or and initial qualification if it is a requal no matter the rank you have 12 months to do it. If its an initial qualification then if your rank is E1, E2, E3, or E4 than you have 30 months to complete it. If its an initial qualification and you are E6, E7, E8, or E9 then you have 18 months. This is what I have and it works for the requalification but for everyone else no matter there rank it gives you 30 months.
Dinq Date: IIf([CommandData]![Requal or Initial]="R",DateAdd("m",12,[Start Date]),IIf([CommandData]![Requal or Initial]="I" And [CommandData]![Rank]="E1" Or "E2" Or "E3" Or "E4",DateAdd("m",30,[Start Date]),IIf([CommandData]![Requal or Initial]="I" And [CommandData]![Rank]="E5" Or "E6" Or "E7" Or "E8" Or "E9",DateAdd("m",18,[Start Date]),"")))
You must use the correct syntax for OR:
[CommandData]![Rank]="E1" Or [CommandData]![Rank]="E2" Or [CommandData]![Rank]="E3" Or [CommandData]![Rank]="E4"
or, shorter:
[CommandData]![Rank] In ("E1", "E2", "E3", "E4")