Iff Statement on Queries

[darreno]

I have a [Date] column and want to populate [FYE] column with the corresponding fiscal year end but can't make it work. Here is the code:

FYE: IIf([Date]<7/1/2007,"2006-2007",IIf([Date]<7/1/2008,"2007-2008",IIf([Date]<7/1/2009,"2008-2009")))

Attached is the DB. Thanks!

 

[Rabbie]

I would reccomend you change the fieldname DATE to something else as it is a reserved word in Access and has been known to cause problems

 

[Rabbie]

Also dates should be enclosed in # signs. Try IIf([Date]<#7/1/2007#,"2006-2007",IIf([Date]<#7/1/2008#,"2007-2008","2008-2009"))

 

[pbaldy]

Rabbie is certainly right about the field name, but I would not want to hard-code the values like that. Try this:

FY: IIf(Month([date])<7,Year([date])-1 & "-" & Year([date]),Year([date]) & "-" & Year([date])+1)

 

[darreno]

Thank you both. Its working now.