Date Serial Issue

[Glencannon4424 via AccessMonster.com]

Hello,

I have what I believe amounts to a Date Serial issue. I have the following
columns in my table:

Hire-Date
Hire-Year
WTD-RATE
Wks-in-Yr

I need to create a calculation for PTO using these fields. If the person was
hired before 2005, their PTO would be calculated as [Weighted Rate] * [Wks-in-
Yr] * 40. If they were hired after 1/1/05, they are subject to different
parameters - if hired before 3/30: [Weighted Rate] * [Wks-in-Yr] * 10, if
hired before 6/30: [Weighted Rate] * [Wks-in-Yr] * 20, if hired before 9/30:
[Weighted Rate] * [Wks-in-Yr] * 30, else: [Weighted Rate] * [Wks-in-Yr] * 40.
I've written the following script:
PTO: IIf([HIRE-YEAR]<2005,((40*[WTD-RATE])/[TBLDAYSOFWEEK.WKS-IN-YR]),IIf(
[HIRE-DATE]<DateSerial(Year(Date()),3,31),((10*[WTD-RATE])/[TBLDAYSOFWEEK.WKS-
IN-YR]),IIf([HIRE-DATE] Between (DateSerial(Year(Date()),3,31)) And
(DateSerial(Year(Date()),6,30)),((20*[WTD-RATE])/[TBLDAYSOFWEEK.WKS-IN-YR]),
IIf([HIRE-DATE] Between (DateSerial(Year(Date()),6,30)) And (DateSerial(Year
(Date()),9,30)),((30*[WTD-RATE])/[TBLDAYSOFWEEK.WKS-IN-YR]),((40*[WTD-RATE])/
[TBLDAYSOFWEEK.WKS-IN-YR])))))

My problem is that I need to do these calculations prospecitively so Access
will only pro-rate these calculations in the year the person is hired. So if
I was hired in Aug 2005, my PTO would be pro-rated for 2005 but I would
receive the whole amount for 2006.

Any help would be very much appreciated!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200602/1

[salad]

Glencannon4424 via AccessMonster.com wrote:

Hello,

I have what I believe amounts to a Date Serial issue. I have the following
columns in my table:

Hire-Date
Hire-Year
WTD-RATE
Wks-in-Yr

I need to create a calculation for PTO using these fields. If the person was
hired before 2005, their PTO would be calculated as [Weighted Rate] * [Wks-in-
Yr] * 40. If they were hired after 1/1/05, they are subject to different
parameters - if hired before 3/30: [Weighted Rate] * [Wks-in-Yr] * 10, if
hired before 6/30: [Weighted Rate] * [Wks-in-Yr] * 20, if hired before 9/30:
[Weighted Rate] * [Wks-in-Yr] * 30, else: [Weighted Rate] * [Wks-in-Yr] * 40.
I've written the following script:
PTO: IIf([HIRE-YEAR]<2005,((40*[WTD-RATE])/[TBLDAYSOFWEEK.WKS-IN-YR]),IIf(
[HIRE-DATE]<DateSerial(Year(Date()),3,31),((10*[WTD-RATE])/[TBLDAYSOFWEEK.WKS-
IN-YR]),IIf([HIRE-DATE] Between (DateSerial(Year(Date()),3,31)) And
(DateSerial(Year(Date()),6,30)),((20*[WTD-RATE])/[TBLDAYSOFWEEK.WKS-IN-YR]),
IIf([HIRE-DATE] Between (DateSerial(Year(Date()),6,30)) And (DateSerial(Year
(Date()),9,30)),((30*[WTD-RATE])/[TBLDAYSOFWEEK.WKS-IN-YR]),((40*[WTD-RATE])/
[TBLDAYSOFWEEK.WKS-IN-YR])))))

My problem is that I need to do these calculations prospecitively so Access
will only pro-rate these calculations in the year the person is hired. So if
I was hired in Aug 2005, my PTO would be pro-rated for 2005 but I would
receive the whole amount for 2006.

Any help would be very much appreciated!

Why don't you write a function? In the query builder, you could enter
something like
CalcIt : PTOCalc([Hire-Date],[Hire-Year],[WTD-RATE],[Wks-in-Yr]

Create a public code module called CalcIt and parse that mess up above
into readable code. I'd hate to debug the IIF statement.

[Glencannon4424 via AccessMonster.com]

Sounds a great suggestion but what is a public code module?

salad wrote:

Hello,

[quoted text clipped - 28 lines]

Any help would be very much appreciated!

Why don't you write a function? In the query builder, you could enter
something like
CalcIt : PTOCalc([Hire-Date],[Hire-Year],[WTD-RATE],[Wks-in-Yr]

Create a public code module called CalcIt and parse that mess up above
into readable code. I'd hate to debug the IIF statement.

--
Message posted via http://www.accessmonster.com

[Larry Linson]

"Glencannon4424 via AccessMonster.com" <u17590@uwe> wrote

Sounds a great suggestion but what is a
public code module?

In Access database window, click the Modules tab, then click "New". Voila,
you will be taken to the module window (sometimes called the Integrated
Development Environment, IDE) with a new public code module opened.

You can create in the General/Declarations declarations of variables and
constants, and in the rest of the module, write VBA code in Function and Sub
procedures. But, if you had to ask the "what is" question, it is likely you
have some learning curve yet to climb before writing any code on your own.

Larry Linson
Microsoft Access MVP