Passing variable to function in VBA module - Resolved

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:



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:




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


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.

x=YearsOfEmployment. With no parameter passed.



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.



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

for my 2 ¢ worth ATNO that deserves a donate

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:



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!

I'm confused now, have you combined the whole lot into a single function?

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.

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"

BTW I would only spend about 3 hours on a problem before trying to get someone else to help, but I admire your resolve

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.

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



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 ).

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?

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.

Good advice. I'm teaching myself, so these little tidbits of advice help. Thanks.

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


Function GetFourYear:

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: 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.

*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.