IF statement for data range

[pauld118]

Hello everyone! I have the following issue.

I have two fields in one table named [TargetDate] and [DateActionClosed]. I'd like to create a querie that will evaluate wich fiscal year represent the date in those fields. For instance, let's say that FY2011 is from 10/1/2010 to 9/31/2011.

If [Target Date] equals # 12/5/2011 # the return value of the IF statement should be FY2011.

If [DateActionClosed] equals # 10/25/2011 # the return value of the IF statement should be FY2012.

How will the querie know when to return FY2011 or FY2012 and so forth. This will be a continuos process, so I don't know if I should do a macro or something like that.

The idea of doing this is to being able to group data of that table by FiscalYear.

I am really lost on this matter. Any help will be appreciated. Thanks!

 

[John Big Booty]

The following shout return the result you are after;

IIf(DatePart("m",[YourDateField]) Between 1 And 9,"FY" & DatePart("yyyy",[YourDateField]),"FY" & DatePart("yyyy",[YourDateField])+1)

 

[John Big Booty]

Here's a small sample DB. Have a look at the query.

 

[pauld118]

Here's a small sample DB. Have a look at the query.

wow!!! It works perfectly!!! You're the best !! Thanks!!!