Register calculated field in table from Form (1 Viewer)

arnelgp · Mar 21, 2018

You cn use Public function ti get the current rate feim your table:

Public function effectiveRate(empID as string,periodEnd as date) as double
Dim rs as dao.recordset
On error resume next
Set rs=currentdb.openrecordset( _
"Select top 1 [monthly rate] from EmpRates where empID='" & empID & " and [effective date]<=#" & format(periodEnd, "mm/dd/yyyy") & "# order by [effective date] desc")
If not (rs.bof and rs.eof) then
effectiveRate=rs(0)
End if
Rs.close
Set rs=nothing
End function

'Xxx
Call the function from current event of form and afterupdate event of empid.

raziel3 · Mar 21, 2018

Pat Hartman said:
Your query will return multiple records. As I said, a history table is difficult to use if it doesn't have an end date.

I came up with this:

DLookUp("[Monthly Rate]","EmpRates","[EMPID] = '" & [cboemp] & "' AND [Effective Date]=#" & DMax("[Effective Date]","EmpRates","[Effective Date]<=#" & [periodend] & "#") & "#")

Is this future proof?

arnelgp I appreciate your suggestion but I can't understand it. In particular 'Select Top 1' and shouldn't the Function update the text box that has the Rate for example I have a text box named [txtmthrate] that have the Dlookup shown above.
In your function shouldn't it have something like
Me.txtmthrate = something??

Pat Hartman · Mar 22, 2018

Assuming there are no syntax errors, that should work. As I said, I wouldn't do it this way. If I were going to keep history, I would keep an end date. That adds complications to the form that manages the rates but simplifies other processes. You only add a record once. Depending on the app, you may use it dozens of times.

raziel3 · Mar 22, 2018

Pat Hartman said:
Assuming there are no syntax errors, that should work. As I said, I wouldn't do it this way. If I were going to keep history, I would keep an end date. That adds complications to the form that manages the rates but simplifies other processes. You only add a record once. Depending on the app, you may use it dozens of times.

I am trying to understand the 'End Date' concept. Wouldn't the 'New' [Effective Date] be the ending for the last Rate? For Example

EMPID_____________RATE______________EFFECTIVE DATE
J.DAWN___________$6200__________________9/1/17
J.DAWN___________$7200__________________4/1/18

In the table above 3/31/18 would be the 'End Date' that the Rate of $6200 is used, right?

This is how I've setup my Employee Rates Table.

Mark_ · Mar 22, 2018

raziel3 said:
I am trying to understand the 'End Date' concept. Wouldn't the 'New' [Effective Date] be the ending for the last Rate? For Example

EMPID_____________RATE______________EFFECTIVE DATE
J.DAWN___________$6200__________________9/1/17
J.DAWN___________$7200__________________4/1/18

In the table above 3/31/18 would be the 'End Date' that the Rate of $6200 is used, right?

This is how I've setup my Employee Rates Table.

The proper answer is "Maybe".

Employee 1 has a rate of $6200 as of 9/1/17.
Employee 1 stops working as of 11/30/17.
Employee 1 start working as of 2/1/18.

Having an end date lets you know there is NO PAY RATE for the period 12/1/17 to 1/31/18 without needing a "zero value" entry. In most cases this would not be needed but allows for special situations.

Mark_ · Mar 22, 2018

raziel3 said:
I have a Payroll entry Form

[cboemp] references the Employee Name
[Periodend] is the end of the pay period

For ease of use, I would have the query that drives the combobox (cboemp) return the rate for the current period. I would also include the effective date. As you are allowing "Period End" there should also be a "PeriodBegin". How do you currently handle a pay change during the period?

Please note, this is one of the reasons I HATE dealing with payroll. Too many exceptions and strange things can come up...

raziel3 · Mar 22, 2018

Mark_ said:
How do you currently handle a pay change during the period?

Pay Rate changes will only occur at the beginning of each month for Monthly paid employees.

Hourly (Weekly, Fortnightly) employees period ends on a Friday so their new pay rates can change the following Saturday. Additionally their Pay Period runs from Saturday to Friday.

Mark_ · Mar 22, 2018

From a design standpoint, I would work out how to handle changes during a pay period now. Much better to be able to support it and not need it than to find out someone was promised something that your system won't be able to handle. People get rather upset when their pay isn't right.

raziel3 · Mar 22, 2018

Much thanks. I didn't even consider a pay rate change during a period. I will definitely need to put something place (just to be safe).

Pat Hartman · Mar 23, 2018

When you have an end date, you can use a join rather than domain functions or subqueries.

Select ... From tbl1 inner join ratetbl On tbl1.Paydate Between ratetbl.Startdate and ratetbl.Enddate;

Register calculated field in table from Form (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!

raziel3

Registered User.

Pat Hartman

Super Moderator

raziel3

Registered User.

Mark_

Longboard on the internet

Mark_

Longboard on the internet

raziel3

Registered User.

Mark_

Longboard on the internet

raziel3

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread