Trying to switch from really long nested IIf statement but having trouble...

[Merdina]

In the interest of full disclosure, I must admit that this is the first time I have ever tried using Access, let alone worked with VB. I came up with an idea for an access database that will help me keep track of allowed and used vacation/personal time much easier and with fewer errors than doing it manually, as I have been.

I am trying to switch from a text box that pulls from a table created by a query to one that automatically calculates the information needed. Pulling from the query created table is not ideal due to possible status changes changing the way PTO is calculated. If someone changes from FT to PT (or vice versa), I need their allowed vacation amount to update automatically, rather than having to rerun the query to get it figured out.

This is what I have so far. Yes, I know the syntax I'm using is probably incorrect, but this was done using the nested IIf statements as a guide. And my VB book won't be here until sometime near the end of next week. (Yes, I am one of those who will purchase a manual just to complete one project)

Biggest problem at this point is I don't want PTOyear to have to be in a table somewhere - I'd like to just declare it at some point that PTOyear = 2010 (or 2011 next year, and so on...)

If Status.Employees="FT" Then
    If PTOyear-Year(HireDate.Employees)>=20 Then
        AlwdVac.Form1="160"
    Else
    If PTOyear-Year(HireDate.Employees)>=16 Then
        AlwdVac.Form1=(PTOyear-Year(HireDate.Employees))*8
    Else
    IF PTOyear-Year(HireDate.Employees)>=5 Then
        AlwdVac.Form1="120"
    Else
    IF PTOyear-Year(HireDate.Employees)>=2 Then
        AlwdVac.Form1="80"
    Else
    If PTOyear-Year(HireDate.Employees)=1 Then
        If 12-Month(HireDate.Employees)>2 Then
            AlwdVac.Form1="64"
        Else
        AlwdVac.Form1="40"
    Else
    If 12-Month(HireDate.Employees)>2 Then
        AlwdVac.Form1=ROUND((12-Month(HireDate.Employees))*2.67,0)
    Else
    AlwdVac.Form1="0"
Else
If Status.Employees="PT" Then
    If PTOyear-Year(HireDate.Employees)>=20 Then
        AlwdVac.Form1="100"
    Else
    If PTOyear-Year(HireDate.Employees)>=16 Then
        AlwdVac.Form1=(PTOyear-Year(HireDate.Employees))*5
    Else
    IF PTOyear-Year(HireDate.Employees)>=5 Then
        AlwdVac.Form1="75"
    Else
    IF PTOyear-Year(HireDate.Employees)>=2 Then
        AlwdVac.Form1="50"
    Else
    If PTOyear-Year(HireDate.Employees)=1 Then
        If 12-Month(HireDate.Employees)>2 Then
            AlwdVac.Form1="40"
        Else
        AlwdVac.Form1="25"
    Else
    If 12-Month(HireDate.Employees)>2 Then
        AlwdVac.Form1=ROUND((12-Month(HireDate.Employees))*1.67,0)
    Else
    AlwdVac.Form1="0"
Else
If Status.Employees="ARR" Then
    AlwdVac.Form1="0"
Else
If Status.Employees="SAL" THen
    AlwdVac.Form1=""
'This needs to be able to be entered manually, as the allowed vacation and personal for Salaried employees can vary
Else
AlwdVac.Form1="Status Needed"

Thanks in advance for any constructive help you can give me!

[Guido Geurs]

dear,

Where are the "END IF" of the nested IF's?
Please, is it possible to write the code in stadges, it will be much better to understand for us.
like :
============
If .... then
if ... then
...
else
if... then
...
else
...
end if
end if
else
....
....
end if
=====================
br,

[Guido Geurs]

dear,

sorry but quick replay delete the leading spaces.

I resend the structure:

If .... then
   if ... then
      ...
   else
      if... then
         ...
      else
         ...
      end if
   end if
else
   ....
   ....
end if

br,

[Guido Geurs]

dear,

I think i see a struckture and it will be better to use the "select case" instead of all these "if then else"s.

Is it like this?

Select Case Status.Employees
Case "FT"
   Select Case PTOyear - Year(HireDate.Employees)
   Case Is >= 20
      AlwdVac.Form1 = "160"
   Case Is >= 16
      AlwdVac.Form1 = (PTOyear - Year(HireDate.Employees)) * 8
   Case Is >= 5
      AlwdVac.Form1 = "120"
   Case Is >= 2
      AlwdVac.Form1 = "80"
   Case 1
      Select Case 12 - Month(HireDate.Employees)
      Case Is > 2
         AlwdVac.Form1 = "64"
         '§ here i'm losing track: 2x "AlwdVac.Form1 =" ????
         '§ 2x "If 12 - Month(HireDate.Employees) > 2 Then" ?????
      End Select
   End Select
Case "PT"
   Select Case PTOyear - Year(HireDate.Employees)
   Case Is >= 20
      AlwdVac.Form1 = "100"
   Case Is >= 16
      AlwdVac.Form1 = (PTOyear - Year(HireDate.Employees)) * 5
   Case Is >= 5
      AlwdVac.Form1 = "75"
   Case Is >= 2
      AlwdVac.Form1 = "50"
   Case 1
      Select Case 12 - Month(HireDate.Employees)
      Case Is > 2
         AlwdVac.Form1 = "64"
         '§ here i'm losing track: 2x "AlwdVac.Form1 =" ????
         '§ 2x "If 12 - Month(HireDate.Employees) > 2 Then" ?????
      End Select
   End Select
Case "ARR"
   AlwdVac.Form1 = "0"
Case "SAL"
   AlwdVac.Form1 = ""
'........
 

br,

[Merdina]

I understand where and why you got lost. But if I understand your procedure correctly, the code for calculating personal time would be:

Select Case Status.Employees
Case "FT"
     AlwdPers.Form1="16"
Case "PT"
     AlwdPers.Form1="10"
Case "ARR"
     AlwdPers.Form1="0"
Case "SAL"
     AlwdPers.Form1=""

And completing what you started (ignoring PT since it's basically the same as FT) would be:

Select Case Status.Employees
Case "FT"
   Select Case PTOyear - Year(HireDate.Employees)
   Case Is >= 20
      AlwdVac.Form1 = "160"
   Case Is >= 16
      AlwdVac.Form1 = (PTOyear - Year(HireDate.Employees)) * 8
   Case Is >= 5
      AlwdVac.Form1 = "120"
   Case Is >= 2
      AlwdVac.Form1 = "80"
   Case Is = 1
      Select Case 12 - Month(HireDate.Employees)
      Case Is > 2
         AlwdVac.Form1 = "64"
      Case Is <=2
     AlwdVac.Form1= "40"
      End Select
   Case Is = 0
      Select Case 12 - Month(HireDate.Employees)
      Case Is > 2
     AlwdVac.Form1 = ROUND((12-Month(HireDate.Employees))*2.67,0)
      Case Is <= 2
     AlwdVac.Form1 = "0"
      End Select     
   End Select

Which brings up two questions for me...
Do I have to "End Select" the original "Select Case Status.Employees"?

And how do I declare in there that PTOyear = 2010?

Thanks so much for your help!!

[Guido Geurs]

Dear,


Yes, Your structure seems to be working.

Q1 - Yes, a "Select Case" must always be closed with "End Select" like the "If" must always be closed with "End If".
The structure of "Select Case" is :

Select Case ...
   Case x
      ...
   Case y
      ...
   Else Case  (if previous cases are not valid and there must be done something)
End select

Q2 - There are 2 possibilities:
1- if PTOyear = 2010 must not change during the use of the program, set it in the code => PTOyear = 2010.
In this case, if it must change over the years, you must change it in the program and so compile it each time !!!.

I think it's better to set it on the form with a TextBox. (see option 2)

2- if the user must change it set it with a Textbox on the form.
Set it in the program with (if the Textbox name = Text_PTOYear):

PTOyear = Val(Text_PTOYear.Text)

br,