HOW TO/NOT A CLUE

  • Thread starter Thread starter macamarr
  • Start date Start date
M

macamarr

Hello, below is my code for some simple math for my form.
If it's works fine, but if there is a better way feel free
to let me know. My question is related to the code. On my
form I have the allotted value, which is on the sub form
and the tblvac. On my tblEmployee's I have the hire date.
What I would like to do is have the Allotted value update
according to the hire date annually.

Example:
After 1 year of service you are allotted 40hrs of
vacation, 2 years 80, 7 years 120 and 15 years 160. So If
I were hired on 1-1-04 the value of course would be 0, but
on 1-1-05 it would become 40. Probably possible but I
don't have a clue.


Private Sub EndDate_LostFocus()
Days_Used.Value = EndDate - StartDate
Hours_Used.Value = DaysUsed.Value * 10 'ten hour shifts
Remaining.Value = Allotted.Value - Hours_Used.Value
End Sub
 
I think I understand you...

My first thought is why would you need to store the Alotted_Value at all. It
sounds like it would be better to just calculate it, and the
Reamining_Value, on the fly as needed (possibly as part of a query).

HTH
Sam
 
Okay, I would have to agree with you. The only problem is;
I am new at access and I've been driving myself nuts
trying to figure out a better way. I would appreciate any
help you can offer. I am not the greatest at this sort of
thing. I know how to run a query but I do not know all the
tangibles for getting the desired results.
 
Taking your advice I toyed with it a bit. I get one part
of what I need to work.

Earned Vac Hrs: IIf([DaysEmployed]>2554,"80") 'this works
fine.
Earned Vac Hrs: IIf([DaysEmployed]<=4596,"80") Or IIf
([DaysEmployed]>=4597,"120") 'this returns a -1....why

The way I understand it is; if DaysEmployes is equal or
less than 4596 return 80 (works fine) if days employed is
equal or greater than 4597 return 120 (does not work)
-----Original Message-----
Okay, I would have to agree with you. The only problem is;
I am new at access and I've been driving myself nuts
trying to figure out a better way. I would appreciate any
help you can offer. I am not the greatest at this sort of
thing. I know how to run a query but I do not know all the
tangibles for getting the desired results.
Click to expand...
 
macamarr

The simplest expression for the example you are trying to get is...
Earned Vac Hrs: IIf([DaysEmployed]<=4596,80,120)
Notice I removed the ""s from around the 80 and 120... I presume you
want these as numerical values, not as text?

To apply a similar idea to the whole range of possibilities which you
mentioned earlier, you could consider the Choose function, e.g.
VacHrs:
Choose([YearsEmployed]>14,160,[YearsEmployed]>6,120,[YearsEmployed]>1,80,[YearsEmployed]=1,40)

--
Steve Schapel, Microsoft Access MVP

Taking your advice I toyed with it a bit. I get one part
of what I need to work.

Earned Vac Hrs: IIf([DaysEmployed]>2554,"80") 'this works
fine.
Earned Vac Hrs: IIf([DaysEmployed]<=4596,"80") Or IIf
([DaysEmployed]>=4597,"120") 'this returns a -1....why

The way I understand it is; if DaysEmployes is equal or
less than 4596 return 80 (works fine) if days employed is
equal or greater than 4597 return 120 (does not work)

-----Original Message-----
Okay, I would have to agree with you. The only problem
is;

I am new at access and I've been driving myself nuts
trying to figure out a better way. I would appreciate any
help you can offer. I am not the greatest at this sort of
thing. I know how to run a query but I do not know all
the

tangibles for getting the desired results.


Alotted_Value at all. It


of a query).

in

form


.
Click to expand...
Click to expand...
 
Back
Top