What is wrong with this IF?

[DAHMB]

I keep geeting an "Else without If " error in line 163 could some one please point out my error?

Private Sub HoursPay_Exit(Cancel As Integer)
Dim db As DAO.Database, rs As DAO.Recordset
Dim stRateOfPay As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryEmployees")
rs.MoveFirst
Do While Not rs.EOF
    If rs!EmployeeID = Forms!frmOverTime!EmployeeID Then
        If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "Ptlm1"
    Else
        If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 1 > 2 Then
        stRateOfPay = "Ptlm2"
    Else
        If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 2 > 3 Then
        stRateOfPay = "Ptlm3"
    Else
        If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 3 > 4 Then
        stRateOfPay = "Ptlm4"
    Else
        If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 4 Then
        stRateOfPay = "Ptlm5"
    Else
        If rs!Rank = "5" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "Sgt1"
    Else
        If rs!Rank = "5" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
        stRateOfPay = "Sgt2"
    Else
        If rs!Rank = "5" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 Then
        stRateOfPay = "Sgt3"
    Else
        If rs!Rank = "4" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "LT1"
    Else
        If rs!Rank = "4" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
        stRateOfPay = "LT2"
    Else
        If rs!Rank = "4" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 Then
        stRateOfPay = "LT3"
    Else
        If rs!Rank = "3" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "Capt1"
    Else
        If rs!Rank = "3" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 Then
        stRateOfPay = "Capt2"
    Else
        If rs!Rank = "2" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "DC1"
    Else
        If rs!Rank = "2" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
        stRateOfPay = "DC2"
    Else
        If rs!Rank = "2" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 Then
        stRateOfPay = "DC3"
    Else
        If rs!Rank = "1" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "Chief1"
    Else
        If rs!Rank = "1" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
        stRateOfPay = "Chief2"
    Else
        If rs!Rank = "1" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 Then
        stRateOfPay = "Chief2"
    Else
        If rs!Rank = "7" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "Ptlm1"
    Else
        If rs!Rank = "7" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 1 > 2 Then
        stRateOfPay = "Ptlm2"
    Else
        If rs!Rank = "7" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 2 > 3 Then
        stRateOfPay = "Ptlm3"
    Else
        If rs!Rank = "7" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 3 > 4 Then
        stRateOfPay = "Ptlm5"
    Else
        If rs!Rank = "7" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 4 Then
        stRateOfPay = "Ptlm5"
    Else
        If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "Disp1"
    Else
        If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
        stRateOfPay = "Disp2"
    Else
      If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 > 3 Then
        stRateOfPay = "Disp3"
    Else
        If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 3 > 4 Then
        stRateOfPay = "Disp4"
    Else
        If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 > 5 Then
        stRateOfPay = "Disp5"
    Else
        If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 5 Then
        stRateOfPay = "Disp6"
    Else
        If rs!Rank = "9" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "StenoU1"
    Else
        If rs!Rank = "9" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
        stRateOfPay = "StenoU2"
    Else
        If rs!Rank = "9" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 > 3 Then
        stRateOfPay = "StenoU3"
    Else
        If rs!Rank = "9" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 3 > 4 Then
        stRateOfPay = "StenoU4"
    Else
        If rs!Rank = "9" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 Then
        stRateOfPay = "StenoU5"
    Else
        If rs!Rank = "10" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "StenoNonU1"
    Else
        If rs!Rank = "10" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
        stRateOfPay = "StenoNonU2"
    Else
        If rs!Rank = "10" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 > 3 Then
        stRateOfPay = "StenoNonU3"
    Else
        If rs!Rank = "10" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 3 > 4 Then
        stRateOfPay = "StenoNonU4"
    Else
        If rs!Rank = "10" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 Then
        stRateOfPay = "StenoNonU5"
    Else
        If rs!Rank = "11" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "CheifSec1"
    Else
        If rs!Rank = "11" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
        stRateOfPay = "CheifSec2"
    Else
        If rs!Rank = "11" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 > 3 Then
        stRateOfPay = "CheifSec3"
    Else
        If rs!Rank = "11" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 3 > 4 Then
        stRateOfPay = "CheifSec4"
    Else
        If rs!Rank = "11" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 Then
        stRateOfPay = "CheifSec5"
    Else
        If rs!Rank = "12" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
        stRateOfPay = "RecSup1"
    Else
        If rs!Rank = "12" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
        stRateOfPay = "RecSup2"
    Else
        If rs!Rank = "12" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 > 3 Then
        stRateOfPay = "RecSup3"
    Else
        If rs!Rank = "12" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 3 > 4 Then
        stRateOfPay = "RecSup4"
    Else
        If rs!Rank = "12" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 Then
        stRateOfPay = "RecSup5"
    Else
        If rs!Rank = "13" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 Then
        stRateOfPay = "CaseScr1"
    End If
 rs.MoveLast
Else
rs.MoveNext
End If
Loop
rs.Close
Set rs = db.OpenRecordset("tblOvertimeRatesOfPay")
rs.MoveFirst
Do While Not rs.EOF
    If rs!Rank = stRateOfPay Then
    Forms!frmOverTime!RateOfPay = rs!PayRate
rs.MoveLast
Else
rs.MoveNext
End If
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

[NeoPa]

Too many levels of IF.
Code indented incorrectly to check properly (either for you or for us).
Try using ElseIf for most of these instead (or even better Select Case ...).

[NeoPa]

Another thought :
Remember you're working in a database. Why try to code all the data in?

Surely a better way of going about this would be to put this info into a table(s) and work from there. Your choice obviously, but that's what I'd do.

[DonRayner]

It's exactly what your error says. You don't have an opening IF statement for the ELSE on line 163 or the END IF on 165.

The only IF that you have is on line 8 and that is closed on line 161

[DonRayner]

Oops, I better work on my typing speed, NeoPa answered twice while I was composing one message.

[DAHMB]

It's exactly what your error says. You don't have an opening IF statement for the ELSE on line 163 or the END IF on 165.

The only IF that you have is on line 8 and that is closed on line 161
Doesn't the "End IF on line 161 end the If from line 9 and the "Else and End IF" on line 163 and 165 go with the "IF" on line 8????

Please help me correct this.

[NeoPa]

"Confused by replies"

You should understand that titles are likely to be missed. This is well worth saying, but it would be better in the text of your post. No worries in this case as I did notice it eventually.

Actually, each "Else" seems to have a further "If" within it. This means you have a very messy structure which should properly be indented right across the page.

What I was saying earlier, in short, is that you should not even consider "fixing" this. It is wrong in too many ways. It will bite you if you attempt to proceed on such unstructured lines.

If you are averse to using the database itself to make this much simpler for you, then at least consider using :

Select Case True
Case rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" > 1
    stRateOfPay = "Ptlm2"
Case If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 1
    ...
End Select

Having looked more closely at your code now. There are a number of further issues with it. I suggest you start a little simpler and build up from that when you have something working.

I should add that compiling the code before posting it here is expected. I know some people don't know about compiling code, but please ensure you do in future. It saves wasting a good deal of time.

[FishVal]

Dear DAHMB, I would strongly recommend you to heed Neopa's suggestion (post #3), build a relevant lookup table and let database engine do what it is suited for.

A bunch of if's is not the only problem of your code.

[DonRayner]

I took a stab a cleaning it up but it's a lost cause, much easier to start over. Like NeoPa says, create a lookup table to stick all your permeations into and either use a Dlookup() or select query to get your Rank and Datelookup() values to compare against the table.

[DAHMB]

Could anyone help me create a lookup table based on the above? I am learnin all this and do'nt quite follow how to create a lookup table to use in a form that points to another table.
MyHireDate is located in one table and the OTDate located in another.

Any help would be greatly appreciated.

Thanks

[NeoPa]

Can you explain the logic behind what you're trying to achieve in your If statements?

I think we need a clearer understanding of what we're dealing with to determine which type of construct would best suit your problem.

A table may be the best way, but the more I look at the actual data (in your code) and try to work out what you're trying to do, the less sure I am.

It may be possible to simplify it greatly just be the expedient of separating out the two parts of most of the If statements.

If you provide the info requested we can certainly look at what may be a more appropriate construct for you.

[DAHMB]

I have a form called frmOvertime that I use to pull data in from a query then I want to check the employees rank and if it is equal to a certain rank and their years in that rank equal or are less then the nnumber I list I want to fill their pay grade field with a value I pull from the tblOvertimePayRates tabl

That is it.

[NeoPa]

OK. Starting to make some sense.

1. Can you tell me why rs!HireDate is used sometimes but rs!RankDate is used at others?
2. I have also noticed that the text associated with each rs.Rank is consistent (If = 1 then always "Chief" etc). Is this also true for the year values?
3. Do you have a table anywhere that has the rank description ("Chief", "DC", "Capt", etc) mapped to the numeric Rank values you're using?

[DAHMB]

Thanks Neo

Answers are as follows:

A. I use rs!HireDate for employees who have two different ranks but have the same paygrade. This way I only map one raygrade to each rank and when I update the date of rank it won't restart the count of years in rank.

B. Yes the vaule for each rank is consistant and no the year vaules are not consistant.

C. Yes I do have a mapped table with the rank numbers assigned to the rank.

Thanks for your help.