Are you saying that if the Years text box has a valid value, it returns the
correct value, but if Years is empty or is a value outside the range of the
function, it returns #Error?
As to Class modules. They are easier than you think. I was intimidated by
them for a long time. This is one I use. We have a lot of information our
system used based on where we are in the accounting calander. Our accounting
periods are not based on the month. Some have 4 weeks, some 5 and some 6. I
was having to do a lot of DLookups and coding in my forms and reports to get
the correct information. So I came up with this. It uses a one row table
that tells me what the date the current accounting period ends on. I then
use the Initialize Event to read the tables and load the data into
varialbes. Then the Property Get statements return those values.
The name of the module is cisDateValues. Class modules are different from
standard modules in that the name of the module is significant. It becomes
the name of the class, so to set a reference to it:
Set clsDates = New cisDateVaules
Then if I need one of the propertys, say the number of hours in the current
week:
= clsDates.HoursInWeek
'---------------------------------------------------------------------------------------
' Module : cisDateValues
' DateTime : 7/28/2006 10:08
' Author : Dave Hargis
' Purpose : Returns information from tbllkacct table
'---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Option Base 0
'Data for the month
Private intCurrMonth As Integer
Private intCurrYear As Integer
Private intWeeksInMonth As Integer
Private intMonthHours As Integer
Private dtmMonthEndDate As Date
'Current Week Info
Private dtmWeekEndDate As Date
Private intWeekNumber As Integer
Private intHoursInWeek As Integer
Private intHoursMTD As Integer
Private intHoursYTD As Integer
Private intHoursRemaining As Integer
Property Get CurrentMonth() As Integer
CurrentMonth = intCurrMonth
End Property
Property Get CurrentYear() As Integer
CurrentYear = intCurrYear
End Property
Property Get WeeksInMonth() As Integer
WeeksInMonth = intWeeksInMonth
End Property
Property Get MonthHours() As Integer
MonthHours = intMonthHours
End Property
Property Get MonthEndDate() As Date
MonthEndDate = dtmMonthEndDate
End Property
Property Get WeekEndDate() As Date
dtmWeekEndDate = dtmWeekEndDate
End Property
Property Get WeekNumber() As Integer
WeekNumber = intWeekNumber
End Property
Property Get HoursInWeek() As Integer
HoursInWeek = intHoursInWeek
End Property
Property Get HoursMTD() As Integer
HoursMTD = intHoursMTD
End Property
Property Get HoursYTD() As Integer
HoursYTD = intHoursYTD
End Property
Property Get HoursRemaining() As Integer
HoursRemaining = intHoursRemaining
End Property
Private Sub Class_Initialize()
Dim rst As Recordset
'Get Current Period Info
dtmMonthEndDate = DLookup("[wedate]", "tblwedate")
Set rst = CurrentDb.OpenRecordset("tbllkacctdate", dbOpenDynaset)
rst.FindFirst "[acctwedate] = #" & dtmMonthEndDate & "#"
intCurrMonth = CInt(rst![mon])
intCurrYear = year(rst![acctmon])
intWeeksInMonth = rst![WksPerMon]
intMonthHours = rst![HrsPerMonth]
dtmMonthEndDate = rst![acctmon]
'Get Current Week INfo
rst.FindFirst "[acctwedate] = #" & DateAdd("d", vbFriday - DatePart("w",
date), _
date) & "#"
dtmWeekEndDate = rst![AcctWedate]
intWeekNumber = rst![MonWkNum]
intHoursInWeek = rst![WeekHours]
intHoursMTD = rst![mtdhrs]
intHoursYTD = rst![YtdHrs]
intHoursRemaining = rst![remaining hrs]
rst.Close
Set rst = Nothing
End Sub
:
I tried to change it from "Null" to 0 but it still returned the #Error. Could
it be something with the format of that control?
I wouldn't even know where to begin to write a class module. VB is not my
strongest point right now. Would it be possible to give me a base line to
start with?
:
If you are getting the error, you may try changing it so it returns 0 instead
of Null where it now returns Null.
Yes, you could modify it to pull the data from a table. Without examining
it in detail, my first thought would be to write a Class module that would
open the table and load the values into variables that become properties of
the class. It would make the function remain clean because you can change
the hard coded values to the property that would represent the years of
service.
Now, if you also want to be able to change the years of service requirements
without chaning the code, you could use the same approach.
:
ok now it's working perfectly!
But I have 2 follow up questions...
1. If there is no value in my textbox "Years" then it returns an #Error in
the "HoursAccrued" textbox (This is the textbox that is calling the
function). How can I make that just say "0" instead of the error?
2. Can the case lines where the years are defined be pulled from a table?
This way if things were to change I could easily just change them in the
table instead of hard coding them into the function.
:
Sorry Squirrel, I did not test the code before, I have now, and made a
change. If you pass it anything not in the list, it returns Null.
Function VacHoursEarned(varYears As Variant) As Variant
Select Case varYears
Case Is = 1
VacHoursEarned = 40
Case 2 To 6
VacHoursEarned = 80
Case 7 To 11
VacHoursEarned = 120
Case Is >= 12
VacHoursEarned = 160
Case Else
VacHoursEarned = Null
End Select
End Function
:
Ok I set it up but now I'm getting a "syntax error" in the function code.
It's turning the Case lines to red.
:
To show the vacation earned in your text box. Make this the control souce
for the text box. It will pass the value from the Years text box to it:
=VacHoursEarned([Years])
:
Thanks for that Klatuu but I have one more question. How do I tell this code
to look at my textbox "Years" to find out which case it falls into?
:
Assuming it is not a bound field (and it should not be, because it violates
the rule on storing calculated values), you can put it in the Control Source
property for the text box. To ensure the values needed are available, see
the modification below:
(Also I noticed the function had a missing line of code, so it would never
return anything
Function VacHoursEarned(varYears AS Variant) As Integer
If IsNull(varYears) Then
VacHoursEarned = Null
Exit Function
End If
Select Case varYears
Case is 1
VacHoursEarned = 40
Case is 2 To 6
VacHoursEarned = 80
Case is 7 To 11
VacHoursEarned = 120
Case is >=12
VacHoursEarned = 160
End Select
End Function
:
How do I call this function from the textbox where I want it to populate the
hours earned?
:
If you want to use the value in a text box, just pass the value in the text
box as the function's argument:
VacHoursEarned(Me.txtYrsService)
:
Hi,
I want to use your function to determine the Vacation hours earned but I'm
unsure how to have it look at the textbox on my form to determine which case
it should use. Can you help shed some light on how I would set this up? I
have a form with a textbox on it that calculates a persons time of employment
from their hiredate until the present date. Now I want the data in that field
to be compared to the select case function you wrote below and have it
populate the hours earned into another textbox.
Thanks
SS
:
Do you have a field that shows how much time has been taken for the current
year?
There is probably a little complexity here in that your vacation allotments
are probably calculated on an employee's original hire date, but what time is
actually used is calculated on a calendar date (Jan 1 - Dec 31).
In any case, here is a function originally written to determine a person's
age as of a date. It will work for you in determining how long a person has
been employed.
Function CalcAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
Dim dtmDOB As Date
Dim dtmAsOf As Date
Dim dtmBDay As Date 'Birthday in the year of calculation.
CalcAge = Null 'Initialize to Null
'Validate parameters
If IsDate(varDOB) Then
dtmDOB = varDOB
If IsDate(varAsOf) Then 'Date to calculate age from.
dtmAsOf = varAsOf
Else
dtmAsOf = date
End If
If dtmAsOf >= dtmDOB Then 'Calculate only if it's after person was
