By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

What is wrong with this IF?

100+
P: 147
I keep geeting an "Else without If " error in line 163 could some one please point out my error?
Expand|Select|Wrap|Line Numbers
  1. Private Sub HoursPay_Exit(Cancel As Integer)
  2. Dim db As DAO.Database, rs As DAO.Recordset
  3. Dim stRateOfPay As String
  4. Set db = CurrentDb()
  5. Set rs = db.OpenRecordset("qryEmployees")
  6. rs.MoveFirst
  7. Do While Not rs.EOF
  8.     If rs!EmployeeID = Forms!frmOverTime!EmployeeID Then
  9.         If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" > 1 Then
  10.         stRateOfPay = "Ptlm1"
  11.     Else
  12.         If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 1 > 2 Then
  13.         stRateOfPay = "Ptlm2"
  14.     Else
  15.         If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 2 > 3 Then
  16.         stRateOfPay = "Ptlm3"
  17.     Else
  18.         If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 3 > 4 Then
  19.         stRateOfPay = "Ptlm4"
  20.     Else
  21.         If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 4 Then
  22.         stRateOfPay = "Ptlm5"
  23.     Else
  24.         If rs!Rank = "5" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
  25.         stRateOfPay = "Sgt1"
  26.     Else
  27.         If rs!Rank = "5" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
  28.         stRateOfPay = "Sgt2"
  29.     Else
  30.         If rs!Rank = "5" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 Then
  31.         stRateOfPay = "Sgt3"
  32.     Else
  33.         If rs!Rank = "4" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
  34.         stRateOfPay = "LT1"
  35.     Else
  36.         If rs!Rank = "4" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
  37.         stRateOfPay = "LT2"
  38.     Else
  39.         If rs!Rank = "4" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 Then
  40.         stRateOfPay = "LT3"
  41.     Else
  42.         If rs!Rank = "3" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
  43.         stRateOfPay = "Capt1"
  44.     Else
  45.         If rs!Rank = "3" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 Then
  46.         stRateOfPay = "Capt2"
  47.     Else
  48.         If rs!Rank = "2" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
  49.         stRateOfPay = "DC1"
  50.     Else
  51.         If rs!Rank = "2" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
  52.         stRateOfPay = "DC2"
  53.     Else
  54.         If rs!Rank = "2" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 Then
  55.         stRateOfPay = "DC3"
  56.     Else
  57.         If rs!Rank = "1" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
  58.         stRateOfPay = "Chief1"
  59.     Else
  60.         If rs!Rank = "1" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
  61.         stRateOfPay = "Chief2"
  62.     Else
  63.         If rs!Rank = "1" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 Then
  64.         stRateOfPay = "Chief2"
  65.     Else
  66.         If rs!Rank = "7" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" > 1 Then
  67.         stRateOfPay = "Ptlm1"
  68.     Else
  69.         If rs!Rank = "7" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 1 > 2 Then
  70.         stRateOfPay = "Ptlm2"
  71.     Else
  72.         If rs!Rank = "7" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 2 > 3 Then
  73.         stRateOfPay = "Ptlm3"
  74.     Else
  75.         If rs!Rank = "7" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 3 > 4 Then
  76.         stRateOfPay = "Ptlm5"
  77.     Else
  78.         If rs!Rank = "7" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 4 Then
  79.         stRateOfPay = "Ptlm5"
  80.     Else
  81.         If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
  82.         stRateOfPay = "Disp1"
  83.     Else
  84.         If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
  85.         stRateOfPay = "Disp2"
  86.     Else
  87.       If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 > 3 Then
  88.         stRateOfPay = "Disp3"
  89.     Else
  90.         If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 3 > 4 Then
  91.         stRateOfPay = "Disp4"
  92.     Else
  93.         If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 > 5 Then
  94.         stRateOfPay = "Disp5"
  95.     Else
  96.         If rs!Rank = "8" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 5 Then
  97.         stRateOfPay = "Disp6"
  98.     Else
  99.         If rs!Rank = "9" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
  100.         stRateOfPay = "StenoU1"
  101.     Else
  102.         If rs!Rank = "9" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
  103.         stRateOfPay = "StenoU2"
  104.     Else
  105.         If rs!Rank = "9" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 > 3 Then
  106.         stRateOfPay = "StenoU3"
  107.     Else
  108.         If rs!Rank = "9" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 3 > 4 Then
  109.         stRateOfPay = "StenoU4"
  110.     Else
  111.         If rs!Rank = "9" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 Then
  112.         stRateOfPay = "StenoU5"
  113.     Else
  114.         If rs!Rank = "10" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
  115.         stRateOfPay = "StenoNonU1"
  116.     Else
  117.         If rs!Rank = "10" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
  118.         stRateOfPay = "StenoNonU2"
  119.     Else
  120.         If rs!Rank = "10" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 > 3 Then
  121.         stRateOfPay = "StenoNonU3"
  122.     Else
  123.         If rs!Rank = "10" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 3 > 4 Then
  124.         stRateOfPay = "StenoNonU4"
  125.     Else
  126.         If rs!Rank = "10" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 Then
  127.         stRateOfPay = "StenoNonU5"
  128.     Else
  129.         If rs!Rank = "11" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
  130.         stRateOfPay = "CheifSec1"
  131.     Else
  132.         If rs!Rank = "11" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
  133.         stRateOfPay = "CheifSec2"
  134.     Else
  135.         If rs!Rank = "11" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 > 3 Then
  136.         stRateOfPay = "CheifSec3"
  137.     Else
  138.         If rs!Rank = "11" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 3 > 4 Then
  139.         stRateOfPay = "CheifSec4"
  140.     Else
  141.         If rs!Rank = "11" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 Then
  142.         stRateOfPay = "CheifSec5"
  143.     Else
  144.         If rs!Rank = "12" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" > 1 Then
  145.         stRateOfPay = "RecSup1"
  146.     Else
  147.         If rs!Rank = "12" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 1 > 2 Then
  148.         stRateOfPay = "RecSup2"
  149.     Else
  150.         If rs!Rank = "12" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 2 > 3 Then
  151.         stRateOfPay = "RecSup3"
  152.     Else
  153.         If rs!Rank = "12" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 3 > 4 Then
  154.         stRateOfPay = "RecSup4"
  155.     Else
  156.         If rs!Rank = "12" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 Then
  157.         stRateOfPay = "RecSup5"
  158.     Else
  159.         If rs!Rank = "13" And "Datediff(Y,#" & rs!RankDate & "#,#" & OTDate & "#)" < 4 Then
  160.         stRateOfPay = "CaseScr1"
  161.     End If
  162.  rs.MoveLast
  163. Else
  164. rs.MoveNext
  165. End If
  166. Loop
  167. rs.Close
  168. Set rs = db.OpenRecordset("tblOvertimeRatesOfPay")
  169. rs.MoveFirst
  170. Do While Not rs.EOF
  171.     If rs!Rank = stRateOfPay Then
  172.     Forms!frmOverTime!RateOfPay = rs!PayRate
  173. rs.MoveLast
  174. Else
  175. rs.MoveNext
  176. End If
  177. Loop
  178. rs.Close
  179. Set rs = Nothing
  180. Set db = Nothing
  181. End Sub
Jan 20 '09 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,489
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 ...).
Jan 20 '09 #2

NeoPa
Expert Mod 15k+
P: 31,489
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.
Jan 20 '09 #3

DonRayner
Expert 100+
P: 489
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
Jan 20 '09 #4

DonRayner
Expert 100+
P: 489
Oops, I better work on my typing speed, NeoPa answered twice while I was composing one message.
Jan 20 '09 #5

100+
P: 147
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.
Jan 20 '09 #6

NeoPa
Expert Mod 15k+
P: 31,489
"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 :
Expand|Select|Wrap|Line Numbers
  1. Select Case True
  2. Case rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" > 1
  3.     stRateOfPay = "Ptlm2"
  4. Case If rs!Rank = "6" And "Datediff(Y,#" & rs!HireDate & "#,#" & OTDate & "#)" < 1
  5.     ...
  6. 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.
Jan 20 '09 #7

FishVal
Expert 2.5K+
P: 2,653
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.
Jan 20 '09 #8

DonRayner
Expert 100+
P: 489
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.
Jan 20 '09 #9

100+
P: 147
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
Jan 20 '09 #10

NeoPa
Expert Mod 15k+
P: 31,489
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.
Jan 20 '09 #11

100+
P: 147
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.
Jan 21 '09 #12

NeoPa
Expert Mod 15k+
P: 31,489
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?
Jan 21 '09 #13

100+
P: 147
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.
Jan 21 '09 #14

Post your reply

Sign in to post your reply or Sign up for a free account.