Hi everyone,

I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())

Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.

Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.

Many thanks,

You shouldn't be using Now to start with, use Date() instead

instead of explicitly setting the year, #1/7/2006# call this with a function

thisyear() - you have to include the brackets

now in a module declare a function thisyear

function thisyear() as date
thisyear = #01/07/2006#
end function

this should now work as your current function. Inside this function you now need to put some additional code to set the thisyear date appropriately, based on the current date. If you store the current year startdate in a table somewhere for instance, you can read it from that, so there are lots of options.

Note that in some circumstances Access treats dates as US dates, so
#01/07/06# might be treated as 7th Jan, not 1st July - watch out for this

Thanks Gemma, I will give your option a go.
I wanted to avoid a table option, as I've learn't in many read posts that you shouldn't store a calculation. while this would be an initial start point that I would calc against, it seems messy. I'll try the function approach.

Rich: thanks for the comment, I had changed it for the first expression, but not the second. glad you picked that up.

My Year(Empstartdate)<Year(Date()) is also flawed as it is not taking the 1 Jul into account. I have to use IIf([EmpStartDate]<#1/07/2006# or resullt from new function

Cheers:)

you can do <= (less than or equal to) as the operator to include the relevant date, rather than just <

Needs to calc forward from that date (including) so test less than is correct;)

i.e. if < then calc else

Thanks gemma, the function was the way to go. This is the first module I've ever put together and probably the simplest, but it feels good to be getting the hang of it.

here is the code that does the job, and I don't need the IIf statement in the query anymore.
Option Compare Database
Option Explicit

Public Function ThisLeaveYear(StartDate As Date) As Date
' --------------------------------------------------------------------------
' Name: ThisLeaveYear
' Inputs: StartDate As Date
' Returns: Date
' Author: Lightray
' Date: Nov 5, 2006
' Comment: Accepts an input date and compares it against two constant dates

On Error GoTo Err_ThisLeaveYear

Dim dtLeaveYearSDate As Date
Dim dtLeaveYearEDate As Date

dtLeaveYearSDate = 1 & "/" & 7 & "/" & Year(Date)
dtLeaveYearEDate = 30 & "/" & 6 & "/" & Year(Date) + 1

Select Case StartDate ' Evaluate input StartDate.
Case Is < dtLeaveYearSDate ' Date is prior to 1 Jul (current year)
ThisLeaveYear = dtLeaveYearSDate ' set output Date = 1 Jul (current year)
Case dtLeaveYearSDate To dtLeaveYearEDate ' Input Date is in this Leave Year Range
ThisLeaveYear = StartDate ' set output Date = input Date
Case Is > dtLeaveYearEDate ' Input Date is in next Leave Year
ThisLeaveYear = dtLeaveYearEDate ' condition shouldn't happen, but set to Leave Year EndDate
End Select

Exit_ThisLeaveYear:
Exit Function

Err_ThisLeaveYear:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_ThisLeaveYear
End Select
End Function

Not 100% sure about the best solution for the 3rd Case condition,
appreciate any guidence there

lightray