IIF statement dropping records

[dm5085]

Can anyone help me with the following:
I have an expression in a query that returns a null value if the month is 12 and the day is 1. Expression works properly on all other dates. Can anyone figure out what's wrong with my syntax? All I'm trying to do is add 1 to the recognition year if the date is later than 12/1.

RecYear: IIf([Month]>11,IIf([Day]>1,DatePart("yyyy",[qryUnionGoldCoinAward]![DateReceived])+1),DatePart("yyyy",[qryUnionGoldCoinAward]![DateReceived]))

 

[dhookom]

It looks like you are missing the final false part. Is this what you are attempting to calculate?
RecYear: IIf([Month]>11 AND [Day]>1,DatePart("yyyy",
[qryUnionGoldCoinAward]![DateReceived])+1,
DatePart("yyyy",[qryUnionGoldCoinAward]![DateReceived]))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dm5085]

That was exactly what I was trying to calculate. Thank you!

 

[PHV]

A simpler expression:
RecYear: DatePart("yyyy",[qryUnionGoldCoinAward]![DateReceived]) + IIf([Month]>11 AND [Day]>1,1,0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886