Storing/Reusing arrays?

[ctags009]

OK - It may not be so much that I'm struggling but moreso that I'm just completely lost.

I have a function that finds a value based on a date "asofdate" what I'm looking to do is find the value that the function would return for each of the days of the year. Then once I have all 365/366 values, I'd like to base another function off of those values.

For example: This is my function for finding which "step" an employee is at which changes based on years in position and contractual dates. Since this number can change at any given time, I'd like to have a list of what step a given employee would be at for every day of the year. This way I can base a "salary" calculation off of each of the returned "step" values to find what an employee's salary will be for every day of the year.

Here is my code for the step function:
I highlighted where the date is used.

Function funEmpStep(blnOvrd As Boolean, bytStpOvrd As Byte, bytEmpUnion As Byte, _
                strStpAnvDt, bytStp As Byte, dtPosStrt As Date, [COLOR=red]asOfDate As Date[/COLOR], _
                bytTtlSteps As Byte)
Dim AnnivDate As Date, AnnivStep As Byte
              
    'No Union or Union without Steps
    If Nz(bytEmpUnion, 0) = 0 Then
        'Step Override applies
        If blnOvrd = -1 Then
            funEmpStep = bytStpOvrd
        'No Step Override
        Else
            funEmpStep = "N/A"
        End If
    'In Union with Steps
    Else
        Select Case Nz(strStpAnvDt, "N/A")
            'No anniversary date
            Case "N/A"
                If (Nz(bytStp, 0) + bytStpOvrd) >= Nz(bytTtlSteps, (Nz(bytStp, 0) + bytStpOvrd)) Then
                    funEmpStep = Nz(bytTtlSteps, (Nz(bytStp, 0) + bytStpOvrd))
                Else
                    funEmpStep = Fix(Nz(bytStp, 0) + bytStpOvrd)
                End If
            'Anniversary date
            Case Else
                AnnivDate = IIf(DateSerial(Year(dtPosStrt), Left(strStpAnvDt, 2), Right(strStpAnvDt, 2)) < dtPosStrt, _
                    DateSerial(Year(dtPosStrt) + 1, Left(strStpAnvDt, 2), Right(strStpAnvDt, 2)), _
                    DateSerial(Year(dtPosStrt), Left(strStpAnvDt, 2), Right(strStpAnvDt, 2)))
                AnnivStep = (([COLOR=red]asOfDate[/COLOR] - AnnivDate) / 365.25) + bytStpOvrd
                If (AnnivStep + bytStpOvrd) >= bytTtlSteps Then
                    funEmpStep = bytTtlSteps
                Else
                    funEmpStep = Fix(AnnivStep + bytStpOvrd)
                End If
        End Select
    End If
            
End Function

I messed around with coming up with an array, but I don't know how to go about storing these values and reusing them in another function

Any help would be appreciated!

 

[gemma-the-husky]

oiut of interest, you can get the daynumber of the year directly, so you probably dont need to store any of this stuff in fancy structures. vba will calculate these in nano seconds.

DatePart("y", Date)

 

[ctags009]

oiut of interest, you can get the daynumber of the year directly, so you probably dont need to store any of this stuff in fancy structures. vba will calculate these in nano seconds.

DatePart("y", Date)

What I'm looking to do is have "asofdate" = 01/01/CY then 01/02/CY then 01/03/CY and so on... til 12/31/CY

I started with something like:

dtStrtYear = DateSerial(Year(date), 1,1)
dtEndYear = DateSerial(Year(date) + 1 ,1,1)
dtDayDiff = dtEndYear - dtStrtYear

then perhaps

Redim arrSteps(1 to dtDayDiff)
For i = 1 to dtDayDiff
.... find the step.....
next i


But I dont know what to do with the results to to be able to use them moving forward (i'll like have to do some quick arithmetic to each of the results when I do this later on)

 

[gemma-the-husky]

probably this sort of thing

for dateslong = datevalue(#1/1/2011#) to datevalue(#12/31/2011#)

 

[vbaInet]

Have you considered saving those values in a table and every year you wipe it clean (or back it up) and create new set of values for the new financial year.

Or you can use a collection or dictionary object. It's an associative array.

 

[spikepl]

The above smells of brute-force approach again

As far as I can tell by skimming the code, there is only one or perhaps two changes during the year, so your table/array would hold the same value for each date range.

Why do you need a value for each specifc day of the year?

As to other things - Access is very forgiving apparently, since it lets you get away with

DateSerial(Year(dtPosStrt), Left(strStpAnvDt, 2), Right(strStpAnvDt, 2))

by doing implicit type conversion from String (inside a Variant) to an Integer. It's thus a matter of taste in this case , but I would not rely on such things and rather do it explicitly

 

[vbaInet]

@spikepl: What is so "brute force" about the approach? We're talking about an iteration of 366 or 365. What if the code is being used in a query how long would it take to perform these calculations per record if the values were not saved in a data structure or a table?

I'm not one for saving derived values into tables but in this case it would be beneficial.

 

[spikepl]

I am not arguing about storage of data that is re-used or too complex to calculate in a query - I proposed that myself . I am questioning the need of having/storing a value for each day, since most of the days have the same value. It may or may not be necessary, thus my question. In this context, the brute-force approach is to create data for each day. The alternative is to exploit what one knows about the data and changes therein. This may or may not be viable, I have no clue.

 

[vbaInet]

I am questioning the need of having/storing a value for each day, since most of the days have the same value.

I don't even see any loop in there that performs a calculation on a per-day basis so yes you're right there. The OP needs to clarify exactly what the function does and why he needs to save the values.

 

[ctags009]

I don't even see any loop in there that performs a calculation on a per-day basis so yes you're right there. The OP needs to clarify exactly what the function does and why he needs to save the values.

That's the part I'm looking for help with -

I'm trying to run this function for each day of the year. which will tell me where in a salary range an employee falls. it will either return a value from 0-10 or "N/A" for employees who don't move on a step scale.

@spikepl You're correct, the value will only change once or twice a year, but, the reason I need to run it for each day of the year is to A. find out when exactly it changes, and B to get a an accurate total salary cost for the year (for example, if the salary is 10,000 for the first 3 months, 20,000 for the next 3 months and 30,000 for the final 6 months, i will know that the total salary for that employee is 22,500, a number needed for forecasting total costs for the business)

Also,

DateSerial(Year(dtPosStrt), Left(strStpAnvDt, 2), Right(strStpAnvDt, 2))

Is in there because some employees have a contract that says that they move up on a specific date, so i have that value store in a table as MM/DD and i use the year of the date the employee started working, to find out how many anniversaries they have had based on that contract date.

Perhaps the key would be to add a couple lines of vba that create a table and add 365-366 records and store all of these values per employee, then delete it once ive used them? This is where I start to run in trouble as far as my ability to accomplish this is concerned. I imagine the table (per employee) would end up looking something like:
lngID_____dtDate___________strStep
1_________01/01/2011_________"N/A"
2_________01/02/2011_________"N/A"
3_________01/03/2011_________ 0
.....
365_______12/31/2011_________ 1

Then, in my next function, I would open this recordset and loop through it to find the salary on a given date or average all 365 values to find the ACTUAL salary.

In theory (in my head) - it seems like it may work... but in practice, with my admittedly very limited knowledge of vba and access, I'm not so sure if it's the most efficient way.

Thanks again for all of your help -
Simply answering the questions you guys ask about it helps me to rethink my strategy and I feel I'm learning a lot.

 

[ctags009]

This would have to be a temporary table (deleted after each time I use it) because I need to make this application flexible enough to allow for exceptions. Like, if someone changes position titles and moves into a completely different set of contract rules that start them at a different step or different group of steps all together. In this case I would want to run the (what will eventually be a report) over again to find out the employee's new projected salary total (but will also have to take into consideration what it had cost me already to-date)
An example of this would be:
A police dispatcher works from 01/01/2011 to 05/31/2011 then becomes a Police Officer on 06/01/2011, gets a step increase on 09/01/2011 and finishes the year at that step.

On 06/01/2011 I should be able to figure out what it's going to cost for the next 25 years to have this Police Officer working for me (assuming he doesn't get any promotions aside from contractual step increases), and also including what it cost for the first 6 months as a police dispatcher.

 

[vbaInet]

How many different types of contracts do you have?

 

[ctags009]

At the moment there are 8 separate contracts, all a bit different- only one of which as an anniversary date override...

 

[spikepl]

I need to run it for each day of the year WHY ? is to A. find out when exactly it changes (by scanning all days in a year?), and B to get a an accurate total salary cost for the year (for example, if the salary is 10,000 for the first 3 months, 20,000 for the next 3 months and 30,000 for the final 6 months, i will know that the total salary for that employee is 22,500, a number needed for forecasting total costs for the business So why does this require creation of 365 numbers? )

With the context provided I still do not see the need of throwing information away and creating 365 numbers. You approach is akin to grabbing a calendar, and writing the salary for each day of the year, before being able to answer the question "What is the total salary this year".

You know what the salary is for each day - it is the same as the previous day and all the other preceding days, since the last change. You have the change dates, you know what the length of the period is, and that is all that's needed. Maybe there is some other need, but so far I don't see any justification for doing it day by day

Wrt to

DateSerial(Year(dtPosStrt), Left(strStpAnvDt, 2), Right(strStpAnvDt, 2))

It is useful to look up the documentation once in a while.

[B]DateSerial[/B][B]([/B][B][I]year, month, day[/I][/B][B])[/B]
 The [B]DateSerial[/B] function syntax has these arguments:
                [B]Argument[/B]       [B]Description[/B]                
                [B][I]year[/I][/B]                 Required. [B]Integer[/B]. Number between 100 and 9999, inclusive, or a numeric expression
.                [B][I]month[/I][/B]             Required. [B]Integer[/B]. Any numeric expression.
                 [B][I]day[/I][/B]                  Required. [B]Integer[/B]. Any numeric expression.

The function is to be fed with 3 integers as arguments, not one integer and two strings. Access lets you get away with this and does the type conversion for you, but you should be aware that that is the case.

 

[Galaxiom]

I would be storing information about the contracts that allowed the ultimately required information to be calculated from a date on the fly using a function.

Storing a whole set values is a very clumsy solution.

 

[vbaInet]

I would be storing information about the contracts that allowed the ultimately required information to be calculated from a date on the fly using a function.

Storing a whole set values is a very clumsy solution.

Just what I was getting at. Store the contract type and the corresponding salary scale (or whatever other information that is required) in a table and look it up or link it to your main table.