get data within fiscal year 2

[THWatson]

Using Access 2003

The fiscal year is October 1 through September 30.

tblAttendance holds the data re meetings attended by members.

The user selects two dates in order to get attendance data between those dates.

What I want to do is determine whether or not the date range falls within the proper fiscal year.

Following is the query column

IIf(Month(Date())<=9,Between DateSerial(Year(Date()-1),10,1) And DateSerial(Year(Date(),9,30),Between DateSerial(Year(Date(),10,1) AND DateSerial(Year(Date()+1),9,30)

What this is supposed to do is...
1. If the Month in which we are currently is less than 9 (September) the query checks between October 1 one year ago and September 30 of this year.
2. However, if the Month in which we are currently is October through December, the query checks between October 1 of the current year and September 30 of the next year.

Does the code make sense in that regard?

Thanks.

Tom

 

[THWatson]

As an addendum...

The IIf statement as above returns no records.

The following, without the IIf, returns records.

Between DateSerial(Year(Date())-1,10,1) And DateSerial(Year(Date()),9,30)

Here is the entire SQL for the query...without the IIf

SELECT qryAttendance.AttendanceID, qryAttendance.MemberID, qryAttendance.MeetingDate
FROM qryAttendance
WHERE (((qryAttendance.MeetingDate) Between DateSerial(Year(Date())-1,10,1) And DateSerial(Year(Date()),9,30)));

Tom

 

[PHV]

Between DateSerial(Year(Date())+(Month(Date())<=9),10,1) And DateSerial(Year(Date())-(Month(Date())>10),9,30)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

In case you missed this from PH's post, you can't put the "Between" inside the expression. It won't work.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Thanks, PHV. That's super.

Duane, thanks for drawing attention to that. I gathered that from mulling over PHV's post. Hadn't realized that. Or I had realized it and forgot it, which could also be possible.

Always appreciated.

Tom