Can Someone Help me with this Code?

G

Guest

I am attaching some code that I have been using in a form that has been
working for a while. However, I just went to add some updates to it, and
discovered that it will no longer compile. I am using AccessXP. It stops at
the line
Case "CHUM SALMON", "PINK SALMON", "SOCKEYE SALMON"
and gives me the message 'compile error, case without select case' Does
this have something to do with having three cases on this one line-do I have
to seperate them out? Code follows. All help, is greatly appreciate. cam

PS. It did wrap some lines when I pasted them in. Those ones are on same
line in original code. Thanks again.

Public Function Update_AgeClassByLength()
Select Case [Common Name]
Case "CHINOOK SALMON"
If ([Month] = 5 And [Length] <= 120) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] <=
140) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 8) And [Length] <= 215) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 9 Or [Month] = 10) And [Length] <=
250) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 11) And [Length] <= 280) Then
[Age_ClassByLength] = "subyearling"
ElseIf ([Month] = 5 And ([Length] > 120 And [Length] <=
250)) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 6 Or [Month] = 7) And ([Length] > 140
Or [Length] <= 280)) Then
[Age_ClassByLength] = "yearling"
ElseIf ([Month] = 8 And ([Length] > 215 And [Length] <=
350)) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 9 Or [Month] = 10) And ([Length] >
251 Or [Length] <= 400)) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 11) And ([Length] > 281 Or [Length]
<= 400)) Then
[Age_ClassByLength] = "yearling"
ElseIf ([Month] = 5 And [Length] > 250) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] > 280)
Then
[Age_ClassByLength] = "subadult/adult"
ElseIf ([Month] = 8 And [Length] > 350) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 9 Or [Month] = 10 Or [Month] = 11)
And [Length] > 400) Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "COHO SALMON"
If ([Month] = 5 And [Length] <= 275) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] <=
330) Then
[Age_ClassByLength] = "yearling"
If ([Month] = 8 And [Length] <= 425) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 9 Or [Month] = 10 Or [Month] = 11)
And [Length] <= 450) Then
[Age_ClassByLength] = "yearling"
ElseIf ([Month] = 5 And [Length] > 275) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] > 330)
Then
[Age_ClassByLength] = "subadult/adult"
ElseIf ([Month] = 8 And [Length] > 425) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 9 Or [Month] = 10 Or [Month] = 11)
And [Length] > 450) Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "CHUM SALMON", "PINK SALMON", "SOCKEYE SALMON"
If [Length] <= 350 Then
[Age_ClassByLength] = "juvenile"
ElseIf [Length] > 350 Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "STEELHEAD"
If (([Month] = 5 Or [Month] = 6 Or [Month] = 7) And
[Length] <= 350) Then
[Age_ClassByLength] = "juvenile"
ElseIf (([Month] = 8 Or [Month] = 9 Or [Month] = 10 Or
[Month] = 11) And [Length] <= 400) Then
[Age_ClassByLength] = "juvenile"
ElseIf (([Month] = 5 Or [Month] = 6 Or [Month] = 7) And
[Length] > 350) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 8 Or [Month] = 9 Or [Month] = 10 Or
[Month] = 11) And [Length] > 400) Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "CUTTHROAT TROUT"
[Age_ClassByLength] = "Total"
Case Else
[Age_ClassByLength] = Null

End Select

End Function
 
G

George Nicholson

Case "COHO SALMON"
If ([Month] = 5 And [Length] <= 275) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] <=
330) Then
[Age_ClassByLength] = "yearling"
If ([Month] = 8 And [Length] <= 425) Then
....<snip>.....
End If
Note that there are 2 Ifs, but there is only 1 End If within COHO SALMON.

2 ways to fix: 1)change 2nd If to ElseIf (probably what you intended) or 2)
add a 2nd End If somewhere.

As written the COHO SALMON section doesn't have the same # of Ifs and
EndIfs, so the first line following that section (CHUM SALMON, etc.) gets a
compile error ("case without select case" is a bit misleading, it's really a
"If without EndIf". The compiler only knows that it has too much or too
little of something in the previous section and makes it's best guess as to
what that is.)

Aside from that, you might want to consider an alternative logic construct,
as outlined below. I tried to simply edit your existing code. To a large
extent this may be a matter ot personal preference, but I would find
something along these lines a lot easier to read, debug & maintain. And if
anyone ever needs to actually modify the Length/Class parameter in the
future....

(Note: you have some "Length < x OR Length > y" clauses which should be
ANDs not ORs. If left as ORs they would evaluate to True for any Length
supplied.).

Case "CHINOOK SALMON"
Select Case [Month]
Case 5
If [Length] <= 120) Then [Age_ClassByLength] = "subyearling"
If [Length] > 250) Then [Age_ClassByLength] = "subadult/adult"
If [Length] > 120 And [Length] <= 250 Then [Age_ClassByLength] =
"yearling"
Case 6, 7
If [Length] <= 140 Then [Age_ClassByLength] = "subyearling"
If [Length] > 280 Then [Age_ClassByLength] = "subadult/adult"
If [Length] > 140 Or [Length] <= 280 Then [Age_ClassByLength] =
"yearling"
Case 8
If [Length] <= 215 Then [Age_ClassByLength] = "subyearling"
If [Length] > 350) Then [Age_ClassByLength] = "subadult/adult"
If [Length] > 215 And [Length] <= 350 Then [Age_ClassByLength] =
"yearling"
Case 9,10
If [Length] <= 250) Then [Age_ClassByLength] = "subyearling"
If [Length] > 400 Then [Age_ClassByLength] = "subadult/adult"
If [Length] > 251 Or [Length] <= 400 Then [Age_ClassByLength] =
"yearling"
Case 11
If [Length] <= 280) Then [Age_ClassByLength] = "subyearling"
If [Length] > 400 Then [Age_ClassByLength] = "subadult/adult"
If [Length] > 281 Or [Length] <= 400 Then [Age_ClassByLength] =
"yearling"
Case Else
MsgBox "Error: Unexpected Month Dec - Apr"
End Select

Even this could be modified further with an ElseIf structure or another
(probably faster) Select Case:
Case 5
If [Length] <= 120) Then
[Age_ClassByLength] = "subyearling"
Else If [Length] > 250) Then
[Age_ClassByLength] = "subadult/adult"
Else
[Age_ClassByLength] = "yearling"
End If

Case 6
Select Case [Length]
Case <= 140
[Age_ClassByLength] = "subyearling"
Case > 280
[Age_ClassByLength] = "subadult/adult"
Case Else
[Age_ClassByLength] = "yearling"
End Select


HTH,
--
George Nicholson

Remove 'Junk' from return address.



CAM said:
I am attaching some code that I have been using in a form that has been
working for a while. However, I just went to add some updates to it, and
discovered that it will no longer compile. I am using AccessXP. It stops
at
the line
Case "CHUM SALMON", "PINK SALMON", "SOCKEYE SALMON"
and gives me the message 'compile error, case without select case' Does
this have something to do with having three cases on this one line-do I
have
to seperate them out? Code follows. All help, is greatly appreciate.
cam

PS. It did wrap some lines when I pasted them in. Those ones are on same
line in original code. Thanks again.

Public Function Update_AgeClassByLength()
Select Case [Common Name]
Case "CHINOOK SALMON"
If ([Month] = 5 And [Length] <= 120) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] <=
140) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 8) And [Length] <= 215) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 9 Or [Month] = 10) And [Length] <=
250) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 11) And [Length] <= 280) Then
[Age_ClassByLength] = "subyearling"
ElseIf ([Month] = 5 And ([Length] > 120 And [Length] <=
250)) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 6 Or [Month] = 7) And ([Length] >
140
Or [Length] <= 280)) Then
[Age_ClassByLength] = "yearling"
ElseIf ([Month] = 8 And ([Length] > 215 And [Length] <=
350)) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 9 Or [Month] = 10) And ([Length] >
251 Or [Length] <= 400)) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 11) And ([Length] > 281 Or [Length]
<= 400)) Then
[Age_ClassByLength] = "yearling"
ElseIf ([Month] = 5 And [Length] > 250) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] >
280)
Then
[Age_ClassByLength] = "subadult/adult"
ElseIf ([Month] = 8 And [Length] > 350) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 9 Or [Month] = 10 Or [Month] = 11)
And [Length] > 400) Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "COHO SALMON"
If ([Month] = 5 And [Length] <= 275) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] <=
330) Then
[Age_ClassByLength] = "yearling"
If ([Month] = 8 And [Length] <= 425) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 9 Or [Month] = 10 Or [Month] = 11)
And [Length] <= 450) Then
[Age_ClassByLength] = "yearling"
ElseIf ([Month] = 5 And [Length] > 275) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] >
330)
Then
[Age_ClassByLength] = "subadult/adult"
ElseIf ([Month] = 8 And [Length] > 425) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 9 Or [Month] = 10 Or [Month] = 11)
And [Length] > 450) Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "CHUM SALMON", "PINK SALMON", "SOCKEYE SALMON"
If [Length] <= 350 Then
[Age_ClassByLength] = "juvenile"
ElseIf [Length] > 350 Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "STEELHEAD"
If (([Month] = 5 Or [Month] = 6 Or [Month] = 7) And
[Length] <= 350) Then
[Age_ClassByLength] = "juvenile"
ElseIf (([Month] = 8 Or [Month] = 9 Or [Month] = 10 Or
[Month] = 11) And [Length] <= 400) Then
[Age_ClassByLength] = "juvenile"
ElseIf (([Month] = 5 Or [Month] = 6 Or [Month] = 7) And
[Length] > 350) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 8 Or [Month] = 9 Or [Month] = 10 Or
[Month] = 11) And [Length] > 400) Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "CUTTHROAT TROUT"
[Age_ClassByLength] = "Total"
Case Else
[Age_ClassByLength] = Null

End Select

End Function
Click to expand...
 
G

Guest

George,
Thanks so much. That fixed the problem. Also, thanks for finding that
error in or vs and for some of the yearling length codes. You are right that
your reorganization is much easier to read and I'm not sure why I did it that
way in the first place. Probably because I was so new to writing code at
that time that I didn't see the way of doing a select case within a select
case.

Thanks again, you've been a big help.
cam

George Nicholson said:
Case "COHO SALMON"
If ([Month] = 5 And [Length] <= 275) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] <=
330) Then
[Age_ClassByLength] = "yearling"
If ([Month] = 8 And [Length] <= 425) Then
....<snip>.....
End If
Note that there are 2 Ifs, but there is only 1 End If within COHO SALMON.

2 ways to fix: 1)change 2nd If to ElseIf (probably what you intended) or 2)
add a 2nd End If somewhere.

As written the COHO SALMON section doesn't have the same # of Ifs and
EndIfs, so the first line following that section (CHUM SALMON, etc.) gets a
compile error ("case without select case" is a bit misleading, it's really a
"If without EndIf". The compiler only knows that it has too much or too
little of something in the previous section and makes it's best guess as to
what that is.)

Aside from that, you might want to consider an alternative logic construct,
as outlined below. I tried to simply edit your existing code. To a large
extent this may be a matter ot personal preference, but I would find
something along these lines a lot easier to read, debug & maintain. And if
anyone ever needs to actually modify the Length/Class parameter in the
future....

(Note: you have some "Length < x OR Length > y" clauses which should be
ANDs not ORs. If left as ORs they would evaluate to True for any Length
supplied.).

Case "CHINOOK SALMON"
Select Case [Month]
Case 5
If [Length] <= 120) Then [Age_ClassByLength] = "subyearling"
If [Length] > 250) Then [Age_ClassByLength] = "subadult/adult"
If [Length] > 120 And [Length] <= 250 Then [Age_ClassByLength] =
"yearling"
Case 6, 7
If [Length] <= 140 Then [Age_ClassByLength] = "subyearling"
If [Length] > 280 Then [Age_ClassByLength] = "subadult/adult"
If [Length] > 140 Or [Length] <= 280 Then [Age_ClassByLength] =
"yearling"
Case 8
If [Length] <= 215 Then [Age_ClassByLength] = "subyearling"
If [Length] > 350) Then [Age_ClassByLength] = "subadult/adult"
If [Length] > 215 And [Length] <= 350 Then [Age_ClassByLength] =
"yearling"
Case 9,10
If [Length] <= 250) Then [Age_ClassByLength] = "subyearling"
If [Length] > 400 Then [Age_ClassByLength] = "subadult/adult"
If [Length] > 251 Or [Length] <= 400 Then [Age_ClassByLength] =
"yearling"
Case 11
If [Length] <= 280) Then [Age_ClassByLength] = "subyearling"
If [Length] > 400 Then [Age_ClassByLength] = "subadult/adult"
If [Length] > 281 Or [Length] <= 400 Then [Age_ClassByLength] =
"yearling"
Case Else
MsgBox "Error: Unexpected Month Dec - Apr"
End Select

Even this could be modified further with an ElseIf structure or another
(probably faster) Select Case:
Case 5
If [Length] <= 120) Then
[Age_ClassByLength] = "subyearling"
Else If [Length] > 250) Then
[Age_ClassByLength] = "subadult/adult"
Else
[Age_ClassByLength] = "yearling"
End If

Case 6
Select Case [Length]
Case <= 140
[Age_ClassByLength] = "subyearling"
Case > 280
[Age_ClassByLength] = "subadult/adult"
Case Else
[Age_ClassByLength] = "yearling"
End Select


HTH,
--
George Nicholson

Remove 'Junk' from return address.



CAM said:
I am attaching some code that I have been using in a form that has been
working for a while. However, I just went to add some updates to it, and
discovered that it will no longer compile. I am using AccessXP. It stops
at
the line
Case "CHUM SALMON", "PINK SALMON", "SOCKEYE SALMON"
and gives me the message 'compile error, case without select case' Does
this have something to do with having three cases on this one line-do I
have
to seperate them out? Code follows. All help, is greatly appreciate.
cam

PS. It did wrap some lines when I pasted them in. Those ones are on same
line in original code. Thanks again.

Public Function Update_AgeClassByLength()
Select Case [Common Name]
Case "CHINOOK SALMON"
If ([Month] = 5 And [Length] <= 120) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] <=
140) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 8) And [Length] <= 215) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 9 Or [Month] = 10) And [Length] <=
250) Then
[Age_ClassByLength] = "subyearling"
ElseIf (([Month] = 11) And [Length] <= 280) Then
[Age_ClassByLength] = "subyearling"
ElseIf ([Month] = 5 And ([Length] > 120 And [Length] <=
250)) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 6 Or [Month] = 7) And ([Length] >
140
Or [Length] <= 280)) Then
[Age_ClassByLength] = "yearling"
ElseIf ([Month] = 8 And ([Length] > 215 And [Length] <=
350)) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 9 Or [Month] = 10) And ([Length] >
251 Or [Length] <= 400)) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 11) And ([Length] > 281 Or [Length]
<= 400)) Then
[Age_ClassByLength] = "yearling"
ElseIf ([Month] = 5 And [Length] > 250) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] >
280)
Then
[Age_ClassByLength] = "subadult/adult"
ElseIf ([Month] = 8 And [Length] > 350) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 9 Or [Month] = 10 Or [Month] = 11)
And [Length] > 400) Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "COHO SALMON"
If ([Month] = 5 And [Length] <= 275) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] <=
330) Then
[Age_ClassByLength] = "yearling"
If ([Month] = 8 And [Length] <= 425) Then
[Age_ClassByLength] = "yearling"
ElseIf (([Month] = 9 Or [Month] = 10 Or [Month] = 11)
And [Length] <= 450) Then
[Age_ClassByLength] = "yearling"
ElseIf ([Month] = 5 And [Length] > 275) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 6 Or [Month] = 7) And [Length] >
330)
Then
[Age_ClassByLength] = "subadult/adult"
ElseIf ([Month] = 8 And [Length] > 425) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 9 Or [Month] = 10 Or [Month] = 11)
And [Length] > 450) Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "CHUM SALMON", "PINK SALMON", "SOCKEYE SALMON"
If [Length] <= 350 Then
[Age_ClassByLength] = "juvenile"
ElseIf [Length] > 350 Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "STEELHEAD"
If (([Month] = 5 Or [Month] = 6 Or [Month] = 7) And
[Length] <= 350) Then
[Age_ClassByLength] = "juvenile"
ElseIf (([Month] = 8 Or [Month] = 9 Or [Month] = 10 Or
[Month] = 11) And [Length] <= 400) Then
[Age_ClassByLength] = "juvenile"
ElseIf (([Month] = 5 Or [Month] = 6 Or [Month] = 7) And
[Length] > 350) Then
[Age_ClassByLength] = "subadult/adult"
ElseIf (([Month] = 8 Or [Month] = 9 Or [Month] = 10 Or
[Month] = 11) And [Length] > 400) Then
[Age_ClassByLength] = "subadult/adult"
End If
Case "CUTTHROAT TROUT"
[Age_ClassByLength] = "Total"
Case Else
[Age_ClassByLength] = Null

End Select

End Function
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

calculate sign module 2
Help with code 2
Excel 1 vs 10 in Excel Formula 0
Ideas PLEASE 7
Code Optimization 0
Mysterious SQL statement hating tables 3
Set Text Box Control To Blank 1
Vlookup error 3

Top