Checking Dates in a Function

[cneill]

Hi, I am trying to put together a function to be able to check the following to see if they are above or below target.
The report will run based on the current date
The Year needs to be split into 12 months
The month needs to be between the Start and end date of the month.

So far I have tried this, which works a bit but would need to have seperate code for each month, is there away of looping through all the date periods, so simplify the code.
Any thoughts?

Function TargetStatus()

Dim JanStartDate As String, JanEndDate As String, FebStartDate As String, FebEndDate As String, CurrentDate As String, TCFL As String, TCFLTarget09 As String


CurrentDate = Date
JanStartDate = #1/1/2009#
JanEndDate = #1/31/2009#
TCFL = Reports![RptKAMSchemeKTCO2Performance09]![CFL]
TCFLTarget09 = (Reports![RptKAMSchemeKTCO2Performance09]![CFLTarget09] / 12)

'Jan 09
If CurrentDate >= JanStartDate Then
If CurrentDate <= JanEndDate Then
If TCFL < TCFLTarget09 Then
Reports![RptKAMSchemeKTCO2Performance09]![LabelBelow].Visible = True
Reports![RptKAMSchemeKTCO2Performance09]![LabelAbove].Visible = False
End If
End If
End If

If CurrentDate >= JanStartDate Then
If CurrentDate <= JanEndDate Then
If TCFL > TCFLTarget09 Then
Reports![RptKAMSchemeKTCO2Performance09]![LabelBelow].Visible = False
Reports![RptKAMSchemeKTCO2Performance09]![LabelAbove].Visible = True
End If
End If
End If

'Feb 09
FebStartDate = #2/1/2009#
FebEndDate = #2/28/2009#
TCFL = Reports![RptKAMSchemeKTCO2Performance09]![CFL]
TCFLTarget09 = (Reports![RptKAMSchemeKTCO2Performance09]![CFLTarget09] / 11)

If CurrentDate >= FebStartDate Then
If CurrentDate <= FebEndDate Then
If TCFLTarget09 < TCFL Then
Reports![RptKAMSchemeKTCO2Performance09]![LabelBelow].Visible = True
Reports![RptKAMSchemeKTCO2Performance09]![LabelAbove].Visible = False
End If
End If
End If

If CurrentDate >= FebStartDate Then
If CurrentDate <= FebEndDate Then
If TCFL > TCFLTarget09 Then
Reports![RptKAMSchemeKTCO2Performance09]![LabelBelow].Visible = False
Reports![RptKAMSchemeKTCO2Performance09]![LabelAbove].Visible = True
End If
End If
End If


End Function

 

[PHV]

Have a look at the Month (and the Year ?) function.

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

 

[MajP]

What you have is not a function it is a sub routine or a "method". A function returns a single value this does not return anything. You can write a function that does not return anything, but why bother.


But you should write a function that returns true or false, then you should have other methods call this function passing in two dates.

public function inTarget (startDate as date, endDate as date)as boolean
if Date >= startDate and Date <= endDate then
inTarget = true
end if
end function