Calculate year range function

[bibaudj]

I'm trying to write a function to be used in a query - because I don't think it can be compiled directly in a query (using Expression Builder). This is for a holiday DB. I would like to determine and automatically calculate/ display which holiday year annual leave dates fall into, i.s. if date is 3 Feb 07 that would fall into holiday year 2006/2007, whereas 8 Mar 07 would fall into 2007/2008. Basically in English I think it would be:

Find Year [EndDate] = 'YearIn'
If Date [EndDate] is before 01 March 'YearIn'
Then
'YearRange' = 'YearIn' minus 1 / 'YearIn' (the / being separator not division)
Else
'YearRange' = 'YearIn' / 'YearIn' plus 1

Thanks!

[ADezii]

I'm trying to write a function to be used in a query - because I don't think it can be compiled directly in a query (using Expression Builder). This is for a holiday DB. I would like to determine and automatically calculate/ display which holiday year annual leave dates fall into, i.s. if date is 3 Feb 07 that would fall into holiday year 2006/2007, whereas 8 Mar 07 would fall into 2007/2008. Basically in English I think it would be:

Find Year [EndDate] = 'YearIn'
If Date [EndDate] is before 01 March 'YearIn'
Then
'YearRange' = 'YearIn' minus 1 / 'YearIn' (the / being separator not division)
Else
'YearRange' = 'YearIn' / 'YearIn' plus 1

Thanks!

Here is the Function that will do the job, its associated call, and output:

Public Function fCalculateHolidayYears(MyDate As Date) As String
  If MyDate < CDate("3/1/" & Year(MyDate)) Then
    fCalculateHolidayYears = Year(MyDate) - 1 & "/" & Year(MyDate)
  Else
    fCalculateHolidayYears = Year(MyDate) & "/" & Year(MyDate) + 1
  End If
End Function

Debug.Print fCalculateHolidayYears(#3/8/2007#)
fCalculateHolidayYears([Leave Date]) 'if used in a Calculated Field

Output: 2007/2008

[NeoPa]

I'm trying to write a function to be used in a query - because I don't think it can be compiled directly in a query (using Expression Builder). This is for a holiday DB. I would like to determine and automatically calculate/ display which holiday year annual leave dates fall into, i.s. if date is 3 Feb 07 that would fall into holiday year 2006/2007, whereas 8 Mar 07 would fall into 2007/2008. Basically in English I think it would be:

Find Year [EndDate] = 'YearIn'
If Date [EndDate] is before 01 March 'YearIn'
Then
'YearRange' = 'YearIn' minus 1 / 'YearIn' (the / being separator not division)
Else
'YearRange' = 'YearIn' / 'YearIn' plus 1

Thanks!

In SQL it would be quite straightforward actually.
Say your date field is [MyDate] :

Year([MyDate]) - IIf(Month([MyDate])<3,1,0) AS HolidayYear

[ADezii]

In SQL it would be quite straightforward actually.
Say your date field is [MyDate] :

Year([MyDate]) - IIf(Month([MyDate])<3,1,0) AS HolidayYear

Thanks for the pointer. I was under the impression that he wanted the Holiday Year returned as a String in the Format YYYY/YYYY (2006/2007). I try to avoid the IIF() Function at all costs. It is my understanding that it is notoriously inefficient and slow to execute. Please get back to me with your opinion on this.

[bibaudj]

Thanks peeps: NeoPa and ADezii. Yes, I did want the year returned in text format. I'll try both these out and get back to you.

[NeoPa]

Thanks for the pointer. I was under the impression that he wanted the Holiday Year returned as a String in the Format YYYY/YYYY (2006/2007). I try to avoid the IIF() Function at all costs. It is my understanding that it is notoriously inefficient and slow to execute. Please get back to me with your opinion on this.

Any function executed in SQL will be relatively slow.
The special innefficiency of the IIf() function is that it resolves all three parameters before it decides on which value to return. In some cases that can be a very heavy overhead, depending on what's in there. I can't give you the link (I found it within a link from this site though - Tutorials thread I think) but one of the MVPs has even recommended the use of a bespoke, more efficient, version of this function.
Personally, I think that a religious approach to the situation is generally uncalled for. Except in certain extreme cases (normally found in large data processing rather than form type manipulation) the extra delay is negligible.

[NeoPa]

Thanks peeps: NeoPa and ADezii. Yes, I did want the year returned in text format. I'll try both these out and get back to you.

You may want to let us know which of the two formats (YYYY or YYYY/YYYY) you were after too ;)

[ADezii]

Any function executed in SQL will be relatively slow.
The special innefficiency of the IIf() function is that it resolves all three parameters before it decides on which value to return. In some cases that can be a very heavy overhead, depending on what's in there. I can't give you the link (I found it within a link from this site though - Tutorials thread I think) but one of the MVPs has even recommended the use of a bespoke, more efficient, version of this function.
Personally, I think that a religious approach to the situation is generally uncalled for. Except in certain extreme cases (normally found in large data processing rather than form type manipulation) the extra delay is negligible.

Thanks for the feedback.

[bibaudj]

Hi peeps, many apologies firstly that I haven't got back to you for so long (busy!)

I managed to use the code from NeoPa as a basis with pointer from Dezi and 'cobbled' together what I needed in a calculated query field. When I'm free again I'll post the code/ query set-up I used, just for you to see - but it works a dream for what I wanted and couldn't have done it without your help!

Thanks! ;-)

[NeoPa]

I'm very pleased you're sorted (and the delay posting is not a problem - we understand that people have lives) :)
Please do post the set-up you used that resolved your problem. This always helps other members (of TSDN and the public) when they come looking for solutions.