Passing variable to function in VBA module - Resolved

  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

I'm writing a fairly basic application to automate vacation acrual rates based on years of service and then calculate current year-to-date acrued vacation time based on the employees acrual rate.

I've got the calculation for the length of employment working just fine with this function using DateDiff:

Code: [ Select ]
Public Function YearsOfEmployment(varDateOfHire As Variant) As Integer
  Dim varAge As Variant

  If IsNull(varDateOfHire) Then YearsOfEmployment = 0: Exit Function

  varAge = DateDiff("yyyy", varDateOfHire, Now)
  If Date < DateSerial(Year(Now), Month(varDateOfHire), _
            Day(varDateOfHire)) Then
   varAge = varAge - 1
  End If
  YearsOfEmployment = CInt(varAge)
End Function
  1. Public Function YearsOfEmployment(varDateOfHire As Variant) As Integer
  2.   Dim varAge As Variant
  3.   If IsNull(varDateOfHire) Then YearsOfEmployment = 0: Exit Function
  4.   varAge = DateDiff("yyyy", varDateOfHire, Now)
  5.   If Date < DateSerial(Year(Now), Month(varDateOfHire), _
  6.             Day(varDateOfHire)) Then
  7.    varAge = varAge - 1
  8.   End If
  9.   YearsOfEmployment = CInt(varAge)
  10. End Function


This is in it's own module and is called in an Access 2000 Form by this procedure call:

=YearsOfEmployment([date_of_hire])

(date_of_hire is the value stored in the employee_info table)
This works just fine and returns the current length of employment in years. Now here's where I'm stuck. To determine the employees acrual rate I need to evaluate how many years they have in and assign them the acrual rate they are entitled to. I've tried a variety of ideas, but I think this comes very close, but I'm missing something somewhere. I'm sure it's because the YearsOfEmployment variable is in a different function, and that's where I'm stuck -- getting that calculated result passed into this function:

Code: [ Select ]
Public Function Acrual(ByVal x As Variant) As Variant

  ' Dimension the variable.
   Dim x As Variant
   ' Start the Select Case structure.
   Set x = YearsOfEmployment
   Select Case x

     ' Test to see if x less than or equal to 5.
     Case Is <= 5
      ' Display a message box.
      x = 6.67
      

     ' Test to see if x less than or equal to 10 and greater than 5.
     Case 6 To 10
      x = 10
      
      
     ' Test to see if x lgreater than 10.
     Case Is > 10
      x = 13.33
    Acrual = CInt(x)
    
   End Select
End Function
  1. Public Function Acrual(ByVal x As Variant) As Variant
  2.   ' Dimension the variable.
  3.    Dim x As Variant
  4.    ' Start the Select Case structure.
  5.    Set x = YearsOfEmployment
  6.    Select Case x
  7.      ' Test to see if x less than or equal to 5.
  8.      Case Is <= 5
  9.       ' Display a message box.
  10.       x = 6.67
  11.       
  12.      ' Test to see if x less than or equal to 10 and greater than 5.
  13.      Case 6 To 10
  14.       x = 10
  15.       
  16.       
  17.      ' Test to see if x lgreater than 10.
  18.      Case Is > 10
  19.       x = 13.33
  20.     Acrual = CInt(x)
  21.     
  22.    End Select
  23. End Function



Like I said, I know this really can't be all that complicated to do, but I'm just a beginner whose figuring this stuff out as I go. (I'm only halfway through the VBA book I'm using and still have to work my way through the 2 Volume Access Developer's Handbooks *lol)

Once this part is solved the next step is to multiply the Acrual rate determined in the above procedure by the number of months already past in any given year. I already have the function that determines the number of months working just fine but I'm using an expression and I suspect I'm going to need to change that to a function similar to what I did for YearsOf Employment. THis is the expression I'm using that works
Code: [ Select ]
=IIf(Int(Format("1/1","y"))>Int(Format(Date(),"y")),DateDiff("m","1/1",Date())-1,DateDiff("m","1/1",Date()))


I think I can change that to a function OK, but I think I'm going to come up stuck again trying to get the result for Acrual to be used to calculate the number of vacation hours acrued year-to-date.

Any help would be much appreciated. I've been trying to get this to work for over 8 hours between the last three days. I figured that was enough effort on my own and time to ask for help. I'm probably making it more complicated than it really is.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

Code: [ Select ]
Public Function Acrual(ByVal x As Variant) As Variant
   'skip some stuff
   Set x = YearsOfEmployment
   'skip some stuff
End Function
  1. Public Function Acrual(ByVal x As Variant) As Variant
  2.    'skip some stuff
  3.    Set x = YearsOfEmployment
  4.    'skip some stuff
  5. End Function


x=YearsOfEmployment. With no parameter passed.

Code: [ Select ]
Public Function YearsOfEmployment(varDateOfHire As Variant) As Integer
  Dim varAge As Variant

  If IsNull(varDateOfHire) Then YearsOfEmployment = 0: Exit Function
  'skip some stuff
End Function
  1. Public Function YearsOfEmployment(varDateOfHire As Variant) As Integer
  2.   Dim varAge As Variant
  3.   If IsNull(varDateOfHire) Then YearsOfEmployment = 0: Exit Function
  4.   'skip some stuff
  5. End Function


if the parameter is null (it is because there is nothing passed) return 0 and exit

YearsOfEmployment is not a variable, it is a function and as I understand it it will be reevaluated whenever you call it.

I think you are needing a variable YearsOfEmployment and a function called GetYearsOfEmployment.

Code: [ Select ]
Dim YearsOfEmployment
YearsOfEmployment = GetYearsOfEmployment(parameterName)

'....... later on .......

x = YearsOfEmployment
  1. Dim YearsOfEmployment
  2. YearsOfEmployment = GetYearsOfEmployment(parameterName)
  3. '....... later on .......
  4. x = YearsOfEmployment


Don't hold me to that but I <i>think</i> that is the problem

*removed by because I was being dumb lol* but I hope this helps
  • s15199d
  • Expert
  • Expert
  • User avatar
  • Posts: 524
  • Loc: NC, USA

Post 3+ Months Ago

for my 2 ¢ worth ATNO that deserves a donate
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

Thank you rtm -- I didn't quite follow you at first, but it got me thinking in the right direction, and I solved it. Here's the working function:

Code: [ Select ]
Public Function GetYearsOfEmployment(varDateOfHire As Variant) As Variant
  Dim varAge As Variant

  If IsNull(varDateOfHire) Then GetYearsOfEmployment = 0: Exit Function
 
  ' Calculate the years of employment
 
  varAge = DateDiff("yyyy", varDateOfHire, Now)
  If Date < DateSerial(Year(Now), Month(varDateOfHire), _
            Day(varDateOfHire)) Then
   varAge = varAge - 1
  End If
  GetYearsOfEmployment = CInt(varAge)
 

  
   ' Start the Select Case structure.
  
   Select Case GetYearsOfEmployment

     ' Test to see if GetYearsOfEmployment is less than or equal to 5.
     Case Is <= 5
      GetYearsOfEmployment = 6.67
      

     ' Test to see if GetYearsOfEmployment is less than or equal to 10 and greater than 5.
     Case 6 To 10
      GetYearsOfEmployment = 10
      
      
     ' Test to see if GetYearsOfEmployment is greater than 10.
     Case Is > 10
      GetYearsOfEmployment = 13.33
      
    GetYearsOfEmployment = CVar(GetYearsOfEmployment)
    
   End Select
 
End Function
  1. Public Function GetYearsOfEmployment(varDateOfHire As Variant) As Variant
  2.   Dim varAge As Variant
  3.   If IsNull(varDateOfHire) Then GetYearsOfEmployment = 0: Exit Function
  4.  
  5.   ' Calculate the years of employment
  6.  
  7.   varAge = DateDiff("yyyy", varDateOfHire, Now)
  8.   If Date < DateSerial(Year(Now), Month(varDateOfHire), _
  9.             Day(varDateOfHire)) Then
  10.    varAge = varAge - 1
  11.   End If
  12.   GetYearsOfEmployment = CInt(varAge)
  13.  
  14.   
  15.    ' Start the Select Case structure.
  16.   
  17.    Select Case GetYearsOfEmployment
  18.      ' Test to see if GetYearsOfEmployment is less than or equal to 5.
  19.      Case Is <= 5
  20.       GetYearsOfEmployment = 6.67
  21.       
  22.      ' Test to see if GetYearsOfEmployment is less than or equal to 10 and greater than 5.
  23.      Case 6 To 10
  24.       GetYearsOfEmployment = 10
  25.       
  26.       
  27.      ' Test to see if GetYearsOfEmployment is greater than 10.
  28.      Case Is > 10
  29.       GetYearsOfEmployment = 13.33
  30.       
  31.     GetYearsOfEmployment = CVar(GetYearsOfEmployment)
  32.     
  33.    End Select
  34.  
  35. End Function


Now I can move on to part two *grins. That should go a bit easier (I hope)

*I'm donating you 100 points for that bit of help. Thanks again!
  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

I'm confused now, have you combined the whole lot into a single function?
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

Yes. It was easier than trying to pass the value from one function to another and since it is the control source for a separate text field from the text field for years of service, it was just much easier to do it that way.
  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

Ahhh fair enough then I suppose if it is just running through a preset set of commands then there's no point in adding lots of subroutines and functions.

Good luck with the project ATNO, and thanks for the points, I spent all of mine on the "activities" :roll:

BTW I would only spend about 3 hours on a problem before trying to get someone else to help, but I admire your resolve :wink:
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

I'm back to this project. Last week I got all the functions to correctly perform six different calculations I needed. Now, when the form loads, I need to have it update all records (about 45 total) with the appropriate values of the calculated fields. (I need to update the table to write reports based on the table). I'm having a small struggle with the syntax to do this, and exactly where to put it. I'm assuming either the onLoad or the onOpen event would be the best place, but I'm a little lost.

If it helps, the table name is employee_info, and the table columns that need updated are YearsOfService, MonthsOfService, AcrualRate, ProjectedAcrual, ActualAcrual, DaysRemaining.

(This is in an Access Database, not SQL if it makes a difference in the syntax). Thanks for any help.
  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

I tend to go for VB6 applications with external databases and this makes everything slightly different, so I'm not 100% on this, but I think in you are probably looking at something like

Code: [ Select ]
do while not employee_info.eof
  'run your functions
  'move to next record
loop
  1. do while not employee_info.eof
  2.   'run your functions
  3.   'move to next record
  4. loop


However, if you have fields that are directly calculated from other fields at run-time, then I would suggest that the <b>calculated</b> fields can be considered redundant. If the data is re-calculated everytime the system is run, then there is no need to actually <i>store</i> the values.

Therefore I would call the calculations from custom next and previous buttons (whilst disabling the form default buttons). These custom buttons can be done with the form properties and a button wizard. Basically you will be calculating the values for each record one at a time, as you view them.

This could cause some problems if you are producing reports of the data, although I'm not sure.

From a database design and normalization point of view, this would be the way to go, as the table <i>"contains non-key dependancies"</i>, violating 3rd normal form (I have been studying for my exam you see :wink: ).
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

That makes sense. Actually, while I was waiting for an answer I was continuing to research and have pretty much come to the conclusion that setting up a query for the calculated fields and basing the report off of the query is probably the easiest method. I think I can handle that.

One other question, though. My HR manager threw a kink into this this morning though. The final function GetYearsOfEmployment I posted above does exactly what it is supposed to do. However, for employees who are at 4 and 9 years of service approaching 5 and 10 years respectively, I need to some how compensate for the change in acrual rate from their anniversary date until the end of the year.

For example, one employee is at 4 years, but on September 1st she hits her five year anniversary. So currently she is acruing 6.67 hours per month, but from Sept 1st through Dec 31st she will be acruing 10 hours per month.

Any suggestions on how to approach this?
  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

ATNO/TW wrote:
For example, one employee is at 4 years, but on September 1st she hits her five year anniversary. So currently she is acruing 6.67 hours per month, but from Sept 1st through Dec 31st she will be acruing 10 hours per month.

Any suggestions on how to approach this?


I think I understand what you mean, but not 100% sure. The only thing I can suggest is to approach it in the same way I approach any algorithm design that is confusing me. Sit down and write out the process.

Do it as a flowchart, or pseudocode, however you feel most comfortable. Just write out step by step, structured instructions for the manual process so that <i>anyone</i> can follow them. Once you have done that, half the work is done and you just have to worry about syntax and lexicon, the procedures should become quite easy to write.
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

Good advice. I'm teaching myself, so these little tidbits of advice help. Thanks.
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

I'm almost, there!!! This reminds me so much of story problems from back in high school *lol

OK, rtm, I followed your advice and worked my way through it. It almost works....almost.

I'm using a function GetProjectedAcrued to calculate the projected vacation hours for the year for each employee. If the current record (employee) is in their fourth year of service, they will need to have a calculation for a split rate - the old rate and the new rate (as noted in my post above). So if they are in their forth year, I call function GetFourYear for this calculation. I've debugged all the compile errors and the script makes the calculation, but the result is wrong. The GetFourYear function determines this year's anniversary date, then identifies how many months should be calculated at the old rate of 6.67 and how many months at the new rate of 10 hours per month.

For example sake, I have one employee whose anniversary was in June, so 6 months would be at 6.67 and 6 months at 10, so the equation I'm trying to achieve is (6 * 6.67) + (6 * 10) = 100 (total hours) (Of course I have to do this all with variables, since everyone's anniversary date is different). Like I said it makes a calculation, but in this example the result is 720. I can't for the life of me see where I screwed up to get that calculation. Also, I have tried it with CVar, CInt and CLng, all with the same results. Here are the two functions. Hopefully someone will spot what I'm missing:

Function GetProjectedAcrued
Code: [ Select ]
Public Function GetProjectedAcrued(varDateOfHire As Variant) As Variant
  Dim varAge As Variant

  If IsNull(varDateOfHire) Then GetProjectedAcrued = 0: Exit Function
 
  'Function to determine length of service in months
  
  'Dimension the variable
  Dim tServiceMonths As Double
 
  'Calculate date difference between Date of Hire and Now
  tServiceMonths = (DateDiff("m", varDateOfHire, Now))
  If (DatePart("d", varDateOfHire) > DatePart("d", Now)) Then
   tServiceMonths = tServiceMonths - 1
  End If
  If tServiceMonths < 0 Then
   tServiceMonths = tServiceMonths + 1
  End If
 
  GetProjectedAcrued = CInt(tServiceMonths)
 

  
   ' Start the Select Case structure.
  
   Select Case GetProjectedAcrued

     ' Test to see if x less than or equal to 48.
     Case Is <= 48
      ' Display a message box.
      GetProjectedAcrued = 6.67
      

     ' Test to see if x less than or equal to 108 and greater than 60.
     Case 61 To 108
      GetProjectedAcrued = 10
      
      
     ' Test to see if x is greater than 120.
     Case Is > 120
      GetProjectedAcrued = 13.33
      
     ' Test to see if x less than or equal to 60 and greater than 48 to determine 4 years of service
     Case 49 To 60
      Call GetFourYear([date_of_hire])
      
     ' Test to see if x less than or equal to 120 and greater than 108 to determine 9 years of service
     Case 109 To 120
      GetProjectedAcrued = 10
      
    GetProjectedAcrued = CVar(GetProjectedAcrued)
    
   End Select
   
 
  GetProjectedAcrued = CVar(GetProjectedAcrued) * 12
  GetProjectedAcrued = CInt(GetProjectedAcrued)
End Function
  1. Public Function GetProjectedAcrued(varDateOfHire As Variant) As Variant
  2.   Dim varAge As Variant
  3.   If IsNull(varDateOfHire) Then GetProjectedAcrued = 0: Exit Function
  4.  
  5.   'Function to determine length of service in months
  6.   
  7.   'Dimension the variable
  8.   Dim tServiceMonths As Double
  9.  
  10.   'Calculate date difference between Date of Hire and Now
  11.   tServiceMonths = (DateDiff("m", varDateOfHire, Now))
  12.   If (DatePart("d", varDateOfHire) > DatePart("d", Now)) Then
  13.    tServiceMonths = tServiceMonths - 1
  14.   End If
  15.   If tServiceMonths < 0 Then
  16.    tServiceMonths = tServiceMonths + 1
  17.   End If
  18.  
  19.   GetProjectedAcrued = CInt(tServiceMonths)
  20.  
  21.   
  22.    ' Start the Select Case structure.
  23.   
  24.    Select Case GetProjectedAcrued
  25.      ' Test to see if x less than or equal to 48.
  26.      Case Is <= 48
  27.       ' Display a message box.
  28.       GetProjectedAcrued = 6.67
  29.       
  30.      ' Test to see if x less than or equal to 108 and greater than 60.
  31.      Case 61 To 108
  32.       GetProjectedAcrued = 10
  33.       
  34.       
  35.      ' Test to see if x is greater than 120.
  36.      Case Is > 120
  37.       GetProjectedAcrued = 13.33
  38.       
  39.      ' Test to see if x less than or equal to 60 and greater than 48 to determine 4 years of service
  40.      Case 49 To 60
  41.       Call GetFourYear([date_of_hire])
  42.       
  43.      ' Test to see if x less than or equal to 120 and greater than 108 to determine 9 years of service
  44.      Case 109 To 120
  45.       GetProjectedAcrued = 10
  46.       
  47.     GetProjectedAcrued = CVar(GetProjectedAcrued)
  48.     
  49.    End Select
  50.    
  51.  
  52.   GetProjectedAcrued = CVar(GetProjectedAcrued) * 12
  53.   GetProjectedAcrued = CInt(GetProjectedAcrued)
  54. End Function


Function GetFourYear:
Code: [ Select ]
Public Function GetFourYear(dteDate As Date) As Date
  ' This function finds the next anniversary of a date.
  ' If the date has already passed for this year, it returns
  ' the date on which the anniversary occurs in the following year.
 
  Dim dteThisYear As Date
 
  ' Find corresponding date this year.
  dteThisYear = DateSerial(Year(Date), Month(dteDate), Day(dteDate))
  ' Determine whether it's already passed.
  'If dteThisYear < Date Then
   'GetFourYear = DateAdd("yyyy", 1, dteThisYear)
  'Else
   GetFourYear = CDate(dteThisYear)
  'End If
 
  Dim tMonths As Double
  Dim varYearEnd As Date
  Dim AnniversaryDate As Date
  AnniversaryDate = CDate(GetFourYear)
 
  'Calculate the difference between anniversary date and end of year,
 
  tMonths = (DateDiff("m", AnniversaryDate, #12/31/2004#))
  If (DatePart("d", AnniversaryDate) > DatePart("d", #12/31/2004#)) Then
   tMonths = tMonths - 1
  End If
  If tMonths < 0 Then
   tMonths = tMonths + 1
  End If
 
  GetFourYear = CLng(tMonths)
  GetFourYear = CVar(GetFourYear)
 
 
 
  Dim NewRate As Long
  Dim OldRate As Long
 
  NewRate = GetFourYear
  OldRate = 12 - NewRate
  'add total months at new rate to total months at old rate to get total projected acrual for the year
  Dim TotalRate As Long
  TotalRate = (NewRate * 10) + (OldRate * 6.67)
 
  GetFourYear = CVar(TotalRate)
 
End Function
  1. Public Function GetFourYear(dteDate As Date) As Date
  2.   ' This function finds the next anniversary of a date.
  3.   ' If the date has already passed for this year, it returns
  4.   ' the date on which the anniversary occurs in the following year.
  5.  
  6.   Dim dteThisYear As Date
  7.  
  8.   ' Find corresponding date this year.
  9.   dteThisYear = DateSerial(Year(Date), Month(dteDate), Day(dteDate))
  10.   ' Determine whether it's already passed.
  11.   'If dteThisYear < Date Then
  12.    'GetFourYear = DateAdd("yyyy", 1, dteThisYear)
  13.   'Else
  14.    GetFourYear = CDate(dteThisYear)
  15.   'End If
  16.  
  17.   Dim tMonths As Double
  18.   Dim varYearEnd As Date
  19.   Dim AnniversaryDate As Date
  20.   AnniversaryDate = CDate(GetFourYear)
  21.  
  22.   'Calculate the difference between anniversary date and end of year,
  23.  
  24.   tMonths = (DateDiff("m", AnniversaryDate, #12/31/2004#))
  25.   If (DatePart("d", AnniversaryDate) > DatePart("d", #12/31/2004#)) Then
  26.    tMonths = tMonths - 1
  27.   End If
  28.   If tMonths < 0 Then
  29.    tMonths = tMonths + 1
  30.   End If
  31.  
  32.   GetFourYear = CLng(tMonths)
  33.   GetFourYear = CVar(GetFourYear)
  34.  
  35.  
  36.  
  37.   Dim NewRate As Long
  38.   Dim OldRate As Long
  39.  
  40.   NewRate = GetFourYear
  41.   OldRate = 12 - NewRate
  42.   'add total months at new rate to total months at old rate to get total projected acrual for the year
  43.   Dim TotalRate As Long
  44.   TotalRate = (NewRate * 10) + (OldRate * 6.67)
  45.  
  46.   GetFourYear = CVar(TotalRate)
  47.  
  48. End Function
  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

Again, I'm not sure what the problem is, but I would go through this in either debug step-into (or step-over - which ever executes each line individually) mode, or I would start adding in msgbox()'s to display the values of variables.

This line:
Code: [ Select ]
TotalRate = (NewRate * 10) + (OldRate * 6.67)
You know to be correct, so at some point along the line the variables are getting calculated incorrectly.

Go though an example one line at a time, calculating each step manually, and see at what point your answers start to differ from the program's.

Hope it helps.
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

*lol at myself. I've been walking through the VBA handbook for the last month or so, and they do the message box thing for every example. Now I'm sitting here laughing because it didn't even occur to me to do that. I'll work through that in the AM. Thanks again.
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

rtm -God Bless you and the MsgBox() *grins!!!

I got it! Actually, I discovered that the second function was almost written correctly. When I elliminated these two lines the calculation for TotalRate was correct:

Code: [ Select ]
GetFourYear = CLng(tMonths)
  GetFourYear = CVar(GetFourYear)
  1. GetFourYear = CLng(tMonths)
  2.   GetFourYear = CVar(GetFourYear)


The problem with the last line of the GetFourYear function, though was it kept returning a date. I redefined the function As Variant instead of Date and changed the last line of the function to:

Code: [ Select ]
GetFourYear = CDec(TotalRate)


Finally in the GetProjectedAcrued function I changed For Case 49 To 60 to:

Code: [ Select ]
Case 49 To 60
      GetProjectedAcrued = CVar(GetFourYear([date_of_hire])): Exit Function
  1. Case 49 To 60
  2.       GetProjectedAcrued = CVar(GetFourYear([date_of_hire])): Exit Function


The only thing left to do to complete the application is to do the same thing for Case 109 To 120 for the nine year employees, but that's a no-brainer now. I completely appreciate your guiding my thought processes through this little exercise. Specifically, I'm greatful that you didn't just write the code for me and forced me to work through it. This is the first VBA application I've ever written from scratch and at the moment I'm feeling pretty good about it with much thanks to you.

Cheers!


One small last question for this line:
Code: [ Select ]
tMonths = (DateDiff("m", AnniversaryDate, #12/31/2004#))


for #12/31/2004# I tried to set that for just 12/31 so that it defaults to the current year, but the VB editor keeps tacking on the year 2004. I'm sure there has to be a way to do this so it doesn't have to be manually changed each year. Do you (or anyone) know how?
  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

:D debugging is a pain until you can find a set of techniques that work for you. Even then it can be a nightmare. Personally I think the only thing going for VB (other than ease of use) is the debugging tools included. Glad you got it sorted.


ATNO/TW wrote:
One small last question for this line:
Code: [ Select ]
tMonths = (DateDiff("m", AnniversaryDate, #12/31/2004#))


for #12/31/2004# I tried to set that for just 12/31 so that it defaults to the current year, but the VB editor keeps tacking on the year 2004. I'm sure there has to be a way to do this so it doesn't have to be manually changed each year. Do you (or anyone) know how?


I havn't used datediff before, and TBH I havn't done a lot of work with date manipulation. However, I checked the MSDN entry:

http://msdn.microsoft.com/library/defau ... tediff.asp

and it looks as though you can make the <i>Date2</i> into a variable.

I guess to set this variable the best way would be to get the current year (from today's date), and then add that to the month and date ("12/31"). It might even be possible to create this as a string and then convert the string into a date/time variable.

Like I said I'm not to familiar with using dates in VB, but that is the direction i would try to go in.
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

Thanks. I'll look a little closer into that. I did try it as a variable, but I was getting the same result. Whenever I would set the variable, it would still default to 2004. I'm sure I was doing something wrong. And don't worry, this was my first VB script, so up until now I hadn't ever worked with date manipulation either, but the lesson will come in handy, because I need to do some date manipulatoin with some PHP scripts I'm working on.

Again, thanks for your help.
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

To make this complete, here's how I set the Current year December 31 as a variable:

Code: [ Select ]
Dim myDate As Date
  myDate = DateValue("December 31")
  1. Dim myDate As Date
  2.   myDate = DateValue("December 31")


DateValue converts the string to a date and since the year is not specified it assumes the current year based on system time. Then I just used the myDate variable for Date2

Post Information

  • Total Posts in this topic: 19 posts
  • Users browsing this forum: No registered users and 139 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.